SQL Server：转换子选择查询以加入

I have 2 two tables questionpool and question where question is a many to one of question pool. I have created a query using a sub select query which returns the correct random results but I need to return more than one column from the question table.

我有2个两个表问题池和问题哪里问题是问题池的多对一。我使用子选择查询创建了一个查询,该查询返回正确的随机结果,但我需要从问题表中返回多个列。

The intent of the query is to return a random test from the 'question' table for each 'QuizID' from the 'Question Pool' table.

查询的目的是从'Question'表中为'Question Pool'表中的每个'QuizID'返回一个随机测试。

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

4 个解决方案

#1

OUTER APPLY is suited to this:

外部适用适用于此:

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

Another example of OUTER APPLY use http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html

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

Live test: http://www.sqlfiddle.com/#!3/d8afc/1

现场测试: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

#2

Not familiar with SQL server, but I hope this would do:

不熟悉SQL服务器,但我希望这样做:

Select QuestionPool.QuestionPoolID, v.QuestionPoolID, v.xxx -- etc
FROM   QuestionPool
JOIN
       (
        SELECT   TOP (1) *
        FROM     Question
        WHERE    Question.GroupID = QuestionPool.QuestionPoolID
        ORDER BY NEWID()
       ) AS v ON v.QuestionPoolID = QuestionPool.QuestionPoolID
WHERE  QuestionPool.QuizID = '5'

#3

Your query appears to be bringing back an arbitrary Question.QuestionPoolId for each QuestionPool.QuestionPoolId subject to the QuizId filter.

您的查询似乎为QuizId过滤器的每个QuestionPool.QuestionPoolId带回任意Question.QuestionPoolId。

I think the following query does this:

我认为以下查询执行此操作:

select qp.QuestionPoolId, max(q.QuestionPoolId) as any_QuestionPoolId
from Question q join
     qp.QuestionPoolId qp
     on q.GroupId = qp.QuestionPoolId
 WHERE QuestionPool.QuizID = '5' 
group by qp.QuestionPoolId

This returns a particular question.

这会返回一个特定问题。

The following query would allow you to get more fields:

以下查询将允许您获取更多字段:

select qp.QuestionPoolId, q.*
from (select q.*, row_number() over (partition by GroupId order by (select NULL)) as randrownum
      from Question q
     ) join
     (select qp.QuestionPoolId, max(QuetionPool qp
     on q.GroupId = qp.QuestionPoolId
 WHERE QuestionPool.QuizID = '5' and
       randrownum = 1

This uses the row_number() to arbitrarily enumerate the rows. The "Select NULL" provides the random ordering (alternatively, you could use "order by GroupId".

这使用row_number()来任意枚举行。 “选择NULL”提供随机排序(或者,您可以使用“按GroupId排序”)。

#4

Common Table Expressions (CTEs) are rather handy for this type of thing...

通用表格表达式(CTE)对于这类事物来说非常方便......

http://msdn.microsoft.com/en-us/library/ms175972(v=sql.90).aspx

#1