sql-server SQL对UNION中的结果进行排序,并使它们以UNION语句的顺序显示

0x6upsns  于 2022-10-31  发布在  其他
关注(0)|答案(3)|浏览(244)

我不能想出语法来获得结果的顺序与工会。我所追求的是,如果第一个工会有一个结果,它必须出现在第一,如果第二个有一个结果,它必须出现在第二,依此类推。

select a.id, a.id2 
from Table1 a
where   a.id3=(select c1.id1 from table c1 where c1.name='A') --Must be first result
UNION
select distinct a.id, a.id2 
from Table1 a
where   a.id3=(select c2.id2 from table c2 where c2.name='B')--Must be second result (if exists)
UNION
select distinct a.id, a.id2 
from Table1 a
where   a.id3=(select c3.id3 from table c3 where c3.name='C')--Must be 3rd result (if exists)

现在如果第二个联盟没有结果,第三个将是第二个..有人能请指导吗?

qlvxas9a

qlvxas9a1#

我觉得你把这个问题搞得太复杂了,你就不能把JOIN变成table,然后把ORDER BY变成name吗?

SELECT T1.id,
       T1.id2
FROM dbo.Table1 T1
     JOIN dbo.[Table] T ON T1.id3 = T.id
WHERE T.Name IN ('A','B','C')
ORDER BY T.Name ASC;

如果最终得到多个JOIN,则可以使用聚合,而不是DISTINCT

SELECT T1.id,
       T1.id2
FROM dbo.Table1 T1
     JOIN dbo.[Table] T ON T1.id3 = T.id
WHERE T.Name IN ('A','B','C')
GROUP BY T1.id,
         T1.id2
ORDER BY MIN(T.Name) ASC;
9lowa7mx

9lowa7mx2#

Larnu的答案肯定上级您的并集(而且您可能希望使用UNION ALL而不是UNION,除非您的数据模型真的非常混乱,以至于到处都是这些重复项)。
但是,使用UNION/UNION ALL时要记住的一个简单方法是:

SELECT [set] = 1, ... FROM dbo.table1
UNION ALL
SELECT [set] = 2, ... FROM dbo.table2
UNION ALL
SELECT [set] = 3, ... FROM dbo.table3
ORDER BY [set], ...;

这会在每个集合中放置一个排序列,并允许您先按该列控制顺序,然后再按其他任何列控制顺序。

k5hmc34c

k5hmc34c3#

UNION不保证其组的顺序。SQL引擎将对结果集进行分组,然后以随机顺序返回。
为了根据sql输入强制进行排序,可以使用ORDER BY列id或id2(如果相关)。否则,应添加另一列以显式排序结果集:

SELECT id, id2 FROM (
select a.id, a.id2, 1 as customOrder 
from Table1 a
where   a.id3=(select c1.id1 from table c1 where c1.name='A') --Must be first result
UNION
select distinct a.id, a.id2, 2 as customOrder 
from Table1 a
where   a.id3=(select c2.id2 from table c2 where c2.name='B')--Must be second result (if exists)
UNION
select distinct a.id, a.id2, 3 as customOrder 
from Table1 a
where   a.id3=(select c3.id3 from table c3 where c3.name='C')--Must be 3rd result (if exists)
) src 
ORDER BY src.customOrder

相关问题