ORDER BY 与不同数据集的 UNION (T-SQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1004293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORDER BY with a UNION of disparate datasets (T-SQL)
提问by NateJ
I have a query that UNION's two somewhat similar datasets, but they both have some columns that are not present in the other (i.e., the columns have NULL values in the resulting UNION.)
我有一个查询，它UNION是两个有些相似的数据集，但它们都有一些列在另一个中不存在（即，这些列在结果中具有 NULL 值UNION。）
The problem is, I need to ORDERthe resulting data using those columns that only exist in one or the other set, to get the data in a friendly format for the software-side.
问题是，我需要ORDER使用仅存在于一组或另一组中的那些列来生成结果数据，以便以友好的格式获取软件端的数据。
For example: Table1has fields ID, Cat, Price. Table2has fields ID, Name, Abbrv. The IDfield is common between the two tables.
例如：Table1有 fields ID, Cat, Price。表 2有字段ID, Name, Abbrv。该ID字段在两个表之间是通用的。
My query looks like something like this:
我的查询看起来像这样：
SELECT t1.ID, t1.Cat, t1.Price, NULL as Name, NULL as Abbrv FROM t1
UNION
SELECT t2.ID, NULL as Cat, NULL as Price, t2.Name, t2.Abbrv FROM t2
ORDER BY Price DESC, Abbrv ASC
The ORDER BYis where I'm stuck. The data looks like this:
这ORDER BY是我被困的地方。数据如下所示：
100 Balls 1.53
200 Bubbles 1.24
100 RedBall 101RB
100 BlueBall 102BB
200 RedWand 201RW
200 BlueWand 202BW
...but I want it to look like this:
...但我希望它看起来像这样：
100 Balls 1.53
100 RedBall 101RB
100 BlueBall 102BB
200 Bubbles 1.24
200 RedWand 201RW
200 BlueWand 202BW
I'm hoping this can be done in T-SQL.
我希望这可以在 T-SQL 中完成。
回答by JeffO
Select ID, Cat, Price, Name, Abbrv
From
(SELECT t1.ID, t1.Cat, t1.Price, t1.Price AS SortPrice, NULL as Name, NULL as Abbrv
FROM t1
UNION
SELECT t2.ID, NULL as Cat, NULL as Price, t1.Price as SortPrice, t2.Name, t2.Abbrv
FROM t2
inner join t1 on t2.id = t1.id
) t3
ORDER BY SortPrice DESC, Abbrv ASC
Somehow you have to know the data in table 2 are linked to table 1 and share the price. Since the Null in abbrv will come first, there is no need to create a SortAbbrv column.
不知何故，您必须知道表 2 中的数据与表 1 相关联并共享价格。由于 abbrv 中的 Null 将首先出现，因此无需创建 SortAbbrv 列。
回答by Amy B
You should use UNION ALL instead of UNION to save the cost of duplicate checking.
您应该使用 UNION ALL 而不是 UNION 来节省重复检查的成本。
SELECT *
FROM
(
SELECT t1.ID, t1.Cat, t1.Price, NULL as Name, NULL as Abbrv FROM t1
UNION ALL
SELECT t2.ID, NULL as Cat, NULL as Price, t2.Name, t2.Abbrv FROM t2
) as sub
ORDER BY
ID,
CASE WHEN Price is not null THEN 1 ELSE 2 END,
Price DESC,
CASE WHEN Abbrv is not null THEN 1 ELSE 2 END,
Abbrv ASC
回答by Avitus
A quick solution would be to do 2 inserts into a temp table or a table variable and as part of insert into the temp table you can set a flag column to help with sorting and then order by that flag column.
一个快速的解决方案是对临时表或表变量进行 2 次插入，作为插入临时表的一部分，您可以设置一个标志列来帮助排序，然后按该标志列排序。
回答by mundeep
Off the top of my head i would say the worst case scenario is you create a temporary table with all the fields do an INSERT INTO the temp table from both T1 & T2 then SELECT from the temp table with an order by.
在我的脑海里，我会说最坏的情况是你创建一个临时表，其中所有字段都从 T1 和 T2 对临时表进行 INSERT INTO，然后从临时表中按顺序从临时表中选择。
ie. Create a temp table (eg. #temp) with fields Id, Cat, Price, Name, Abbrv, and then:
IE。创建一个临时表（例如#temp），其中包含字段 Id、Cat、Price、Name、Abbrv，然后：
SELECT Id, Cat, Price, null, null INTO #temp FROM T1
SELECT Id, null, null, Name, Abbrv INTO #temp FROM T2
SELECT * FROM #temp ORDER BY Id, Price DESC, Abbrv ASC
NB: I'm not 100% sure on the null syntax from the inserts but i think it will work.
注意：我不是 100% 确定插入的空语法，但我认为它会起作用。
EDIT: Added ordering by Price & Abbrv after id... if Id doesn't link T1 & T2 then what does?
编辑：在 id 之后按价格和缩写添加排序...如果 Id 不链接 T1 和 T2 那么什么？
