将多个连接表中的重叠行折叠成尽可能少的行

Question

Table_Design:

ID
"Alpha"
"Beta"

Table_Size:

Design_ID   Size
"Alpha"     S
"Alpha"     M
"Alpha"     L
"Beta"      S
"Beta"      L

Table_Color:

Design_Id   Color
"Alpha"     "Black"
"Alpha"     "Blue"
"Alpha"     "Red"
"Alpha"     "Green"
"Beta"      "Orange"

select D.ID, S.Size, C.Color from
Table_Design D
Left Outer Join
Table_Size S
ON D.ID = S.Design_Id
Left Outer Join
Table_Color C
ON D.ID = C.Design_Id
where
D.ID = 'Alpha'

Yields:

D.ID    S.Size  C.Color
Alpha   S       Black
Alpha   S       Blue
Alpha   S       Red
Alpha   S       Green
Alpha   M       Black
Alpha   M       Blue
Alpha   M       Red
Alpha   M       Green
Alpha   L       Black
Alpha   L       Blue
Alpha   L       Red
Alpha   L       Green

是否有办法编写一个查询来返回此值：

D.ID    S.Size  C.Color
Alpha   S       Black
Alpha   M       Blue
Alpha   L       Red
Alpha           Green

Answer 1

我假设您想要将带有尺寸的第 n 行与带有颜色的第 n 行组合起来。对于您所展示的表格设计，这是不可能的，因为您无法可靠地识别表格的第 n 行。

我想说的是重新设计：创建一个单独的表来链接具有尺寸和颜色（可以为 NULL）的设计，并且您的查询会容易得多。

Answer 2

我真的不喜欢以这种方式组合数据，但你可以这样做

Select Design_Id, 'Color' as Type, Color as Value from Table_Color where Design_Id = 'Alpha'
union all
Select Design_Id, 'Size' as Type, Size as Value from Table_Size where Design_Id = 'Alpha'

：

Design_Id Type  Value
========= ====  =====
Alpha     Color Black
Alpha     Color Blue
Alpha     Color Red
Alpha     Color Green
Alpha     Size  S
Alpha     Size  M
Alpha     Size  L