排序访问联合查询SQL

2024-05-19 • 问答

我在此数据中应用了联合查询

DRAW NO    C1   C2   C3   C4   C5   C6   C7   C8
DWG-1      D    D    C    C    A    A    B    B
DWG-2      B    B    A

使用此sql

SELECT [DRAW NO],[C1] AS Data FROM Query2
UNION SELECT [DRAW NO],[C2] FROM Query2
UNION SELECT [DRAW NO],[C3] FROM Query2
UNION SELECT [DRAW NO],[C4] FROM Query2
UNION SELECT [DRAW NO],[C5] FROM Query2
UNION SELECT [DRAW NO],[C6] FROM Query2
UNION SELECT [DRAW NO],[C7] FROM Query2
UNION SELECT [DRAW NO],[C8] FROM Query2;

结果是这样的

DRAW NO      DATA
DWG-1        A 
DWG-1        B
DWG-1        C
DWG-1        D
DWG-2        A
DWG-2        B

是否可以将输出更改为这种排序？

DRAW NO      DATA
DWG-1        D 
DWG-1        C
DWG-1        A
DWG-1        B
DWG-2        B
DWG-2        A

从OP的评论中

编辑：
我需要先获取c1的值..然后是c2 ..如果c2具有重复项，则c3应该是下一个值，依此类推。

对于您的示例数据，它将起作用：

SELECT t.[DRAW NO],t.Data FROM (
  SELECT [DRAW NO],1 AS col,[C1] AS Data FROM Query2 UNION  
  SELECT [DRAW NO],2,[C2] FROM Query2 UNION
  SELECT [DRAW NO],3,[C3] FROM Query2 UNION
  SELECT [DRAW NO],4,[C4] FROM Query2 UNION
  SELECT [DRAW NO],5,[C5] FROM Query2 UNION
  SELECT [DRAW NO],6,[C6] FROM Query2 UNION
  SELECT [DRAW NO],7,[C7] FROM Query2 UNION
  SELECT [DRAW NO],8,[C8] FROM Query2
) AS t
WHERE  t.[Data] IS NOT NULL
GROUP BY t.[DRAW NO],t.Data
ORDER BY t.[DRAW NO],MIN(t.col)

结果：

DRAW NO Data
DWG-1   D
DWG-1   C
DWG-1   A
DWG-1   B
DWG-2   B
DWG-2   A

您可以为此使用普通SQL：

SELECT [DRAW NO],[C1] AS Data FROM Query2
UNION 
SELECT [DRAW NO],[C2] FROM Query2
UNION 
SELECT [DRAW NO],[C3] FROM Query2
UNION 
SELECT [DRAW NO],[C4] FROM Query2
UNION 
SELECT [DRAW NO],[C5] FROM Query2
UNION 
SELECT [DRAW NO],[C6] FROM Query2
UNION 
SELECT [DRAW NO],[C7] FROM Query2
UNION 
SELECT [DRAW NO],[C8] FROM Query2

ORDER BY 
[Data]="D",[Data]="C",[Data]="A",[Data]="B";

官方文档：Use a union query to combine multiple queries into a single result

排序访问联合查询SQL

z298961 回答：排序访问联合查询SQL

大家都在问