如何“挑选"使用 T-SQL 随机记录

2021-09-10 00:00:00 sql tsql sql-server

这是一个简单的问题，实际上很难回答，因为采摘"有特殊的含义.

This is a simple question that is actually hard to answer, because the "picking" has a special meaning.

我需要为每个人随机选择三个(并给出选择/行号 1、2 和 3).让人难受的是，persons 和picks 来自不同的表，并且person 和picks 之间没有逻辑连接.

I need to give three random picks for each person (and give pick/row number of 1, 2, and 3). What makes it hard is that the persons and picks are from different tables and there is no logical joining between the person and picks.

我能得到的最接近的是:

SELECT TOP 15 database_id, create_date, RowNo, cs.name FROM sys.databases
CROSS apply ( 
  SELECT top 3 Row_number()OVER(ORDER BY (SELECT NULL)) AS RowNo,*
  FROM (SELECT top 3 name from sys.all_views ORDER BY NEWID()) T
  ) cs

我知道上面不是个人和选择，但它是一个有效的 SQL，任何人都可以在不创建个人和选择表的情况下对其进行测试.还有，

I know the above is not person and picks, but it a working SQL that anyone can test it out without creating person and picks tables first. And,

它说明了我面临的问题 --

It illustrates the problem I'm facing --

上面的 SQL 会给每个人相同的选择，而我需要给不同的人不同的选择.

the above SQL will give each person the same picks, whereas I need to give different person different picks.

怎么做?谢谢.

推荐答案

在 CROSS APPLY 中添加相关条件将解决您的问题

Adding a correlated condition inside the CROSS APPLY will solve your problem 

SELECT TOP 15 database_id,
              create_date,
              RowNo,
              cs.NAME
FROM   sys.databases d
       CROSS apply (SELECT TOP 3 Row_number() OVER(ORDER BY (SELECT NULL)) AS RowNo, *
                    FROM   (SELECT TOP 3 NAME
                            FROM   sys.all_views v
                            WHERE  d.NAME = d.NAME --Here
                            ORDER  BY Newid()) T) cs

检查Where子句中的alias名称，LHS和RHS都来自同一个table和同一个column只是对databases表

Check the alias name in Where clause both LHS and RHS are from same table and same column it is just to execute the sub-query for each row in databases table

相关文章