使用不同数据集的 UNION ORDER BY (T-SQL)

发布于 2024-07-24 23:00:44 字数 1257 浏览 3 评论 0原文

我有一个查询，UNION 的两个有些相似的数据集，但它们都有一些在另一个中不存在的列（即，这些列在生成的 UNION 中具有 NULL 值） code>。）

问题是，我需要使用仅存在于一个或另一组中的那些列来ORDER结果数据，以便以软件端友好的格式获取数据。

例如：Table1 具有字段ID、Cat、Price。 Table2 具有字段ID、名称、缩写。 ID 字段在两个表之间是通用的。

我的查询看起来像这样：

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 是我陷入困境的地方。数据看起来像这样：

100   Balls     1.53                       
200   Bubbles   1.24                       
100                     RedBall    101RB   
100                     BlueBall   102BB   
200                     RedWand    201RW   
200                     BlueWand   202BW

...但我希望它看起来像这样：

100   Balls     1.53                       
100                     RedBall    101RB   
100                     BlueBall   102BB   
200   Bubbles   1.24                       
200                     RedWand    201RW   
200                     BlueWand   202BW

我希望这可以在 T-SQL 中完成。

原文

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.)

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.

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

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

I'm hoping this can be done in T-SQL.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌逼全场 2024-07-31 23:00:44

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 列。

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.

回复收藏 0 原文

Hello爱情风 2024-07-31 23:00:44

您应该使用 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

You should use UNION ALL instead of UNION to save the cost of duplicate checking.

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

回复收藏 0 原文

写下不归期 2024-07-31 23:00:44

一个快速的解决方案是对临时表或表变量进行两次插入，作为插入临时表的一部分，您可以设置一个标志列来帮助排序，然后按该标志列进行排序。

回复收藏 0 原文

羁〃客ぐ 2024-07-31 23:00:44

我想说的是，最坏的情况是您创建一个临时表，其中所有字段都从 T1 和 T1 执行 INSERT INTO 临时表。 T2 然后使用 order by 从临时表中进行 SELECT。

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

注意：我不能 100% 确定插入中的 null 语法，但我认为它会起作用。

编辑：添加了按价格和价格排序 id 后的缩写...如果 Id 没有链接 T1 & 那么T2又是什么呢？

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.

ie. Create a temp table (eg. #temp) with fields Id, Cat, Price, Name, Abbrv, and then:

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.

EDIT: Added ordering by Price & Abbrv after id... if Id doesn't link T1 & T2 then what does?

回复收藏 0 原文

杀手六號 2024-07-31 23:00:44

如果您的查询结果预期的结果数量有限，您可以使用 OFFSET / FETCH 关键字。这里假设，结果集不会超过10000行。

此 SQL 语句将在内部对 table1 结果进行排序。
然后它会在内部对 table2 结果进行排序。
并且它将按此顺序合并这些子集。

例如：

select name  from table1 order by columnNameX OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY
union all 
select name   from table2 order by columnNameY OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY

If you have a limited number of results expected from your query results you can use OFFSET / FETCH Keywords. Here assumption is , result set will not exceed 10000 rows.

This SQL Statement will order table1 results internally.
Then it will order table2 results internally.
And it will merge these subsets in this order.

For example :

select name  from table1 order by columnNameX OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY
union all 
select name   from table2 order by columnNameY OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY

回复收藏 0 原文

~没有更多了~