According to the requirements, if the top 20 is taken each time, since it is random, then SQL Server Order by newid() is just, and then all the data is unioned. So I immediately gave the answer:
selecttop 20 *fromxxxwheretype=1orderbynewid() union selecttop 20 *fromxxxwheretype=0orderbynewid()
However, the SQL query analyzer type is wrong and the syntax is wrong. At first glance, it seems that there is no problem:
selecttop 20 *fromxxxwheretype=1orderbynewid()There is no problem if it is executed alone;
The following half sentence has the same grammatical structure, and there are no errors. But unioning these two queries is wrong. It's strange. Since the prompt is that there is a mistake near the order, I will remove the order:
It can be run without it. In other words, the query in the union clause cannot be used with SQL Server Order by. But I have to use order by here, otherwise how can I get out 20 random records
Finally rewrite it into:
SELECT * FROM (select TOP 20 * from where (c='none' or c=null) and (d='none' or d=null) ORDER BY newid() ) a1 UNION SELECT * FROM (select TOP 20 * from where (c='none' or c=null) and (d='none' or d=null) ORDER BY newid()) a2
It looks a bit weird when the statements are long, but they can't be done. I don't know why the order by cannot be used in the union clause in SQL Server, so only the outer layer is wrapped.
This is the end of this article about the reasons why SQL Server2008 Order by cannot be used directly in the union clause. For more related contents of SQL Server2008 Order by cannot be used directly in the union clause, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!