具有不同数据集的UNIOR的ORDER BY(T-SQL) [英] ORDER BY with a UNION of disparate datasets (T-SQL)
问题描述
我有一个查询,查询UNION
的两个稍微相似的数据集,但它们都具有一些其他列中不存在的列(即,结果UNION
中的列具有NULL值.)
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
.)
问题是,我需要使用仅存在于一个或另一个集合中的那些列来ORDER
生成的数据,以便以友好的格式为软件端获取数据.
The problem is, I need to ORDER
the 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.
例如:表1 具有字段ID, Cat, Price
. 表2 具有字段ID, Name, Abbrv
. ID
字段在两个表之间是公用的.
For example: Table1 has fields ID, Cat, Price
. Table2 has fields ID, Name, Abbrv
. The ID
field is common between the two tables.
我的查询看起来像这样:
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
ORDER BY
是我被卡住的地方.数据如下:
The ORDER BY
is where I'm stuck. The data looks like this:
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
我希望这可以在T-SQL中完成.
I'm hoping this can be done in T-SQL.
推荐答案
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
以某种方式您必须知道表2中的数据已链接到表1并共享价格.由于abbrv中的Null首先出现,因此无需创建SortAbbrv列.
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.
这篇关于具有不同数据集的UNIOR的ORDER BY(T-SQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!