工会和工会

Question

联合是给出了正确的结果，而联合则是给了错误的结果。

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以外的行。我不知道它为什么会产生错误的结果。

Answer 1

必须在最外层的查询级别应用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复仇竞赛的获胜者:空格标识符。

复仇: SQL！

命名约定

• 预留
• Blanks
• 隐藏
• 复制
• Unicode

Answer 2

您可以用CASE语句进行条件排序：

select * from (
  select top 20 * from noble_win 
  where [YEAR]=1970 and [SUBJECT] NOT IN ('Economics', 'Chemistry') 
  order by [SUBJECT], [WINNER]
) t1
union all
select * from (
  select top 20 * from noble_win 
  where [YEAR]=1970 and [SUBJECT] IN ('Economics', 'Chemistry')
  order by [SUBJECT], [WINNER] 
) t2
order by 
  case when [SUBJECT] NOT IN ('Economics', 'Chemistry') then 1 else 2 end,
  [SUBJECT], 
  [WINNER]

Answer 3

如果使用union/union all，那么order by必须位于最外层的选择上。但是，你不需要它们。您只需使用表达式和窗口函数：

select nw.* 
from (select nw.*,
             row_number() over (partition by year,
                                             (case when [SUBJECT   ] in ('Economics', 'Chemistry') then 1 else 2 end)
                                order by [SUBJECT   ], [WINNER                ])
                               ) as seqnum
      from noble_win nw
     ) nw
where seqnum <= 20
order by (case when [SUBJECT   ] in ('Economics', 'Chemistry') then 1 else 2 end) desc,
         [WINNER                ]);

帮你改列名！