SQL Server 查询与每个部分的联合和不同的顺序? [英] SQL Server query with union and different order by to each section?

问题描述

我在网站上搜索过这里.这个问题有很多版本的答案.但找不到我在寻找这个特定问题的内容:

I've searched here on the site. and there are many version of answers to this question. But couldn't find what I was looking for for this specific question :

假设 xxx,yyy,zzz 有 1 列 3 行:

lets say that xxx,yyy,zzz has 1 column, 3 rows:

1   
2   
3   

SELECT a FROM xxx  order by a asc
UNION 
SELECT f FROM yyy order by f desc
UNION 
SELECT t FROM zzz order by t asc

所以想要的结果集是:

1
2
3
3
2
1
1
2
3

UNION 附近有语法错误错误.

There is an error of incorrect syntax near UNION.

我知道 union 的顺序问题(并且只有在需要整体 ORDER BY 时才知道如何解决它.)

I'm aware of the order problem with union (and know how to solve it only if an overall ORDER BY is required.)

问题:

我怎样才能得到我想要的输出?

how can I get my desired output?

推荐答案

你必须将single order by 子句应用到整个 union，否则顺序是定义不明确:

You have to apply a single order by clause to the entire union, or else the ordering isn't well defined:

SELECT a,1 as Pos,a as Ord from xxx
UNION ALL
SELECT f,2,-f from yyy
UNION ALL
SELECT t,3,t from zzz
ORDER BY Pos,Ord

但是，-f 可能感觉像是实现相反顺序的肮脏技巧(或者如果包含 NULL 则可能不完全是您想要的)，所以你也可以这样做:

However, the -f might feel like a dirty trick to achieve the opposite ordering (or may not be entirely what you want if NULLs are included), so you could also do:

SELECT a,1 as Pos,a as OrdAsc,0 as OrdDesc from xxx
UNION ALL
SELECT f,2,0,f from yyy
UNION ALL
SELECT t,3,t,0 from zzz
ORDER BY Pos asc,Ord asc,OrdDesc desc

<小时>

我不清楚为什么您认为它没有回答您的问题 - 可能是因为结果集中的附加列?如果是这样，您可以将整个 UNION 安排在子查询中:

create table #xxx (a int not null)
create table #yyy (f int not null)
create table #zzz (t int not null)
insert into #xxx (a) select 1 union all select 2 union all select 3
insert into #yyy (f) select 1 union all select 2 union all select 3
insert into #zzz (t) select 1 union all select 2 union all select 3

SELECT a FROM (
SELECT a,1 as Pos,a as Ord from #xxx
UNION ALL
SELECT f,2,-f from #yyy
UNION ALL
SELECT t,3,t from #zzz
) t
ORDER BY Pos,Ord

结果:

a
----
1
2
3
3
2
1
1
2
3

这篇关于SQL Server 查询与每个部分的联合和不同的顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！