SQL 连接同一个表中的两个选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14648405/
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
Joining two select queries from the same table
提问by mdc
The table contains an ID column, valueHeading column and a value column. I want to separate the value column into two new columns called valueHeading1 and valueHeading2 depending on which type of valueHeading the value has.
该表包含一个 ID 列、一个 valueHeading 列和一个值列。我想根据值具有的 valueHeading 类型将值列分成两个名为 valueHeading1 和 valueHeading2 的新列。
So I want to join this select: Edit: Full join
所以我想加入这个选择： 编辑：完全加入
SELECT ID
,valueHeading
,value as 'valueHeading1'
FROM table1
WHERE valueHeading = 'valueHeading1'
With This select:
使用此选择：
SELECT ID
,value as 'valueHeading2'
FROM table1
WHERE valueHeading = 'valueHeading2'
on their respective ID's. How do I do this?
在他们各自的 ID 上。我该怎么做呢？
Editto illustrate what I want to do:
编辑以说明我想做什么：
Original table:
原表：
ID valueHeading value
0 valueHeading1 a
0 valueHeading2 a
1 valueHeading1 ab
1 valueHeading2 NULL
2 valueHeading1 abcd
2 valueHeading2 abc
New Table:
新表：
ID valueHeading1 valueHeading2
0 a a
1 ab NULL
2 abcd abc
采纳答案by Aleksandr Fedorenko
In SQLServer2005+ possible use PIVOT
在 SQLServer2005+ 中可能使用 PIVOT
SELECT ID, valueHeading1, valueHeading2
FROM
(
SELECT *
FROM dbo.test28
WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
) x
PIVOT
(
MAX(value)
FOR valueHeading IN ([valueHeading1], [valueHeading2])
) p
Demo on SQLFiddle
回答by xlecoustillier
Try something like :
尝试类似：
SELECT ID
, CASE WHEN valueHeading = 'valueHeading1' THEN value ELSE NULL END AS valueHeading1
, CASE WHEN valueHeading = 'valueHeading2' THEN value ELSE NULL END AS valueHeading2
FROM table1
WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
If you want to regroup all values on one row for each ID, you can try :
如果您想将每个 ID 的所有值重新组合在一行中，您可以尝试：
SELECT ID
, MAX(CASE WHEN valueHeading = 'valueHeading1' THEN value ELSE NULL END) AS valueHeading1
, MAX(CASE WHEN valueHeading = 'valueHeading2' THEN value ELSE NULL END) AS valueHeading2
FROM table1
WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
GROUP BY ID
HAVING MAX(CASE WHEN valueHeading = 'valueHeading1' THEN value ELSE NULL END) IS NOT NULL
OR MAX(CASE WHEN valueHeading = 'valueHeading2' THEN value ELSE NULL END) IS NOT NULL
See SQLFiddle. I also tried on Oracle 11g and MSSQL 2012, and it works each time.
请参阅SQLFiddle。我还尝试了 Oracle 11g 和 MSSQL 2012，并且每次都有效。
回答by hkutluay
If you need only join use this. Using case when is elegant way if you don't need join.
如果你只需要加入使用这个。如果您不需要加入，使用 case when 是一种优雅的方式。
SELECT * FROM
(SELECT ID
,valueHeading
,value as 'valueHeading1'
FROM table1
WHERE valueHeading = 'valueHeading1') AS TAB_1,
(SELECT ID
,value as 'valueHeading2'
FROM table1
WHERE valueHeading = 'valueHeading2') AS TAB_2
WHERE TAB_1.ID = TAB_2.ID
回答by Ghost Developer
self join could be a simple solution
自加入可能是一个简单的解决方案
SELECT DISTINCT t1.ID, t1.value as valueHeading1, t2.value as valueHeading2,
FROM table1 t1
INNER JOIN table1 t2 ON t1.ID = t2.ID
WHERE t1.valueHeading <> t2.valueHeading
