Sqlserver: After records are randomly retrieved from the group, the newid and Union conflicts are resolved.

 

I answered a question in csdn just now. The database selects 30 single-choice questions, 10 multiple-choice questions, and 10 judgments.
 

If you do not want to, click the statement:

 

 Select top 30 * from test where typename = 'single-choice 'order by newid ()

Union all

Select top 10 * from test where typename = 'Multiple selects' order by newid ()

Union all

Select top 10 * from test where typename = 'judge 'order by newid ()

 

Later, the landlord reported the following error:

 

Server: Message 156, Level 15, status 1, Row 2
There is a syntax error near the keyword 'join.
Server: Message 156, Level 15, status 1, row 4
There is a syntax error near the keyword 'join.

 

Later I thought about how to use derived table

 

 SELECT * FROM 
(select top 30 * from exam_test where testtype=0 order by newid()) as a
UNION ALL
SELECT * FROM 
(select top 10 * from exam_test where testtype=1 order by newid()) as b
UNION ALL
SELECT * FROM 
(select top 10 * from exam_test where testtype=2 order by newid()) as c

 

It should be okay this time, and I checked it online. It looks like it should be okay. Who knows the landlord again reported an error:

If the statement contains the Union operator, the items in the order by clause must appear in the selection list.

It's depressing. Why not? Can someone tell me the answer? I hope you will not be enlightened (:

To solve the problem, we have to add a newid () column to each group query, which is quite uncomfortable.

 

 SELECT * FROM  
(select top 30 * ,NewID = NEWID() from exam_test where testtype=0 order by newid()) as a
UNION ALL
SELECT * FROM  
(select top 10 * ,NewID = NEWID() from exam_test where testtype=1 order by newid()) as b
UNION ALL
SELECT * FROM  
(select top 10 * ,NewID = NEWID() from exam_test where testtype=2 order by newid()) as c

 

I don't know. Is that okay? It should not work. There is no test environment, sorry.

 

Select * from (select top 30 * from test where typename = 'select'
Union all

Select top 10 * from test where typename = 'Multiple select'

Union all

Select top 10 * from test where typename = '') as a order by newid ()