SQL Server : convert sub select query to join

Question

我有 2 张两张 table questionpool和 question哪里question是多对一的question pool .我使用 sub select 查询创建了一个查询，该查询返回正确的随机结果，但我需要从 question 返回多个列 table 。

查询的目的是从“问题”表中为“问题池”表中的每个“测验ID”返回一个随机测试。

SELECT QuestionPool.QuestionPoolID,
(
SELECT TOP (1) Question.QuestionPoolID
FROM Question
WHERE Question.GroupID = QuestionPool.QuestionPoolID
ORDER BY NEWID()
)
FROM QuestionPool
WHERE QuestionPool.QuizID = '5'

Answer 1

OUTER APPLY 适用于:

Select *
FROM QuestionPool
OUTER APPLY
(
    SELECT TOP 1 * 
    FROM Question
    WHERE Question.GroupID = QuestionPool.QuestionPoolID
    ORDER BY NEWID()
) x
WHERE QuestionPool.QuizID = '5'

OUTER APPLY 使用的另一个例子 http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html

现场测试: http://www.sqlfiddle.com/#!3/d8afc/1

create table m(i int, o varchar(10));
insert into m values
(1,'alpha'),(2,'beta'),(3,'delta');

create table x(i int, j varchar, k varchar(10));

insert into x values
(1,'a','hello'),
(1,'b','howdy'),
(2,'x','great'),
(2,'y','super'),
(3,'i','uber'),
(3,'j','neat'),
(3,'a','nice');


select m.*, '' as sep, r.*
from m
outer apply
(
  select top 1 *
  from x
  where i = m.i
  order by newid()
) r