問題描述

這是一個簡單的問題，實際上很難回答，因為采摘"有特殊的含義.

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，任何人都可以在不創(chuàng)建個人和選擇表的情況下對其進行測試.還有，

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

這篇關于如何“挑選"使用 T-SQL 隨機記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！