oracle SQL：使用不同的“where”子句在同一个表中加入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2867054/
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
SQL: join within same table with different 'where' clause
提问by Pmarcoen
Ok, so the problem I'm facing is this, I have a table with 3 columns : ID, Key and Value.
好的，所以我面临的问题是，我有一个包含 3 列的表：ID、键和值。
ID | Key | Value
================
1 | 1 | ab
1 | 2 | cd
1 | 3 | ef
2 | 1 | gh
2 | 2 | ij
2 | 3 | kl
Now I want to select the value of Keys 1 & 3 for all IDs, the return should be like this
现在我想为所有ID选择Keys 1 & 3的值，返回应该是这样的
ID | 1 | 2
================
1 | ab | ef
2 | gh | kl
So per ID 1 row containing the Values for Keys 1 & 3.
因此，每个 ID 1 行包含键 1 和 3 的值。
I tried using 'join' but since I need to use multiple where clauses I can't figure out how to get this to work ..
我尝试使用“join”，但由于我需要使用多个 where 子句，我无法弄清楚如何让它工作..
回答by OMG Ponies
For Oracle 8i+, use:
对于 Oracle 8i+，请使用：
SELECT t.id,
MAX(CASE WHEN t.key = 1 THEN t.value ELSE NULL END) AS "1",
MAX(CASE WHEN t.key = 2 THEN t.value ELSE NULL END) AS "2"
FROM TABLE t
GROUP BY t.id
For Oracle versions prior, swap the CASE out for DECODE syntax. Oracle didn't add the PIVOT syntax until 11g.
对于之前的 Oracle 版本，将 CASE 换成DECODE 语法。Oracle 直到 11g 才添加 PIVOT 语法。
回答by Daniel Vassallo
Without using pivot queries, you could also join with a subquery, as follows:
在不使用数据透视查询的情况下，您还可以加入子查询，如下所示：
SELECT t.id, MAX(key_1.value) AS '1', MAX(key_3.value) AS '2'
FROM tb t
INNER JOIN (SELECT id, value FROM tb WHERE `key` = 1) key_1 ON (key_1.id = t.id)
INNER JOIN (SELECT id, value FROM tb WHERE `key` = 3) key_3 ON (key_3.id = t.id)
GROUP BY t.id;
Test Case (in MySQL):
测试用例（在 MySQL 中）：
CREATE TABLE tb (`id` int, `key` int, `value` char(2));
INSERT INTO tb VALUES (1, 1, 'ab');
INSERT INTO tb VALUES (1, 2, 'cd');
INSERT INTO tb VALUES (1, 3, 'ef');
INSERT INTO tb VALUES (2, 1, 'gh');
INSERT INTO tb VALUES (2, 2, 'ij');
INSERT INTO tb VALUES (2, 3, 'kl');
Result:
结果：
+------+------+------+
| id | 1 | 2 |
+------+------+------+
| 1 | ab | ef |
| 2 | gh | kl |
+------+------+------+
2 rows in set (0.04 sec)
回答by azz0r
Why can't you just do three queries?
为什么你不能只做三个查询？
If I understand you correctly, your going to have to use a union join.
如果我理解正确，您将不得不使用联合加入。
回答by moi_meme
SELECT ID, VALUE AS v1, S.v2
FROM TABLE
WHERE KEY = 1
JOIN (SELECT ID, VALUE AS v2 FROM TABLE WHERE Key =3) AS S ON TABLE.ID = S.ID
If you need more, add a join for each where...
如果您需要更多，请为每个位置添加一个连接...
