sql - 联盟和所有联盟

联合会给正确的结果，联合会给错误的结果。

SELECT *
  FROM (SELECT TOP 20 *
          FROM noble_win
         WHERE [YEAR ] = 1970
           AND [SUBJECT   ]  NOT IN ('Economics', 'Chemistry')
        ORDER BY [SUBJECT   ],[WINNER                ]) AS A
UNION
SELECT *
  FROM (SELECT TOP 20 *
          FROM noble_win
         WHERE [YEAR ]=1970
           AND [SUBJECT   ] IN ('Economics', 'Chemistry')
        ORDER BY [SUBJECT   ],[WINNER                ]) AS B

该查询首先给出Chemistry和Economics行，然后给出Economics和Chemistry以外的行。但是，我首先要使用Economics和Chemistry以外的行。我不知道为什么会给出错误的结果。

最佳答案

ORDER BY必须在最外部的查询级别应用:

select  * from
(select top 20 *,1 AS ord from noble_win
 where [YEAR ]=1970 and [SUBJECT   ]  NOT IN ('Economics', 'Chemistry')
 order by [SUBJECT   ],[WINNER                ]
)  as A
union
select * from
(select top 20 *, 2 AS ord from noble_win
 where [YEAR ]=1970 and [SUBJECT   ] IN ('Economics', 'Chemistry')
 order by [SUBJECT   ],[WINNER                ]
) as B
order by ord, [SUBJECT   ],[WINNER                ])

您是DBA Revenge竞赛的获胜者:带空格的标识符。

Revenge: The SQL!

关于sql - 联盟和所有联盟，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57348561/