联盟和所有联盟

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

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第一。我不知道为什么会给出错误的结果。

1> Lukasz Szozd..：

---

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竞赛的获胜者：带空格的标识符。

复仇：SQL！

命名约定

已预留

空白

隐

重复

统一码

---

@JohnCappelletti是的，但仍然是level2-不太好，也不可怕。情况可能更糟。例如，使用看起来相同的[unicode字符]（https://en.wikipedia.org/wiki/A_（Cyrillic））-** [demo]（https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b16a0d77459ad53e37f9aa4f5f784f12 ）**