同一张表上的左连接
我不记得如何将表连接到自身..我的表是:
| id | proc | value | kind |
| 1 | 1 | foo | a |
| 2 | 1 | bar | b |
| 3 | 2 | some | a |
我需要检索值 col,其中 proc 是 $proc,kind 是“a”和“b”..好吧,我需要这样做(寻找 proc = 1):
| v_a | v_b |
| foo | bar |
所以我写了这个查询:
SELECT
a.value AS v_a,
b.value AS v_b
FROM
(SELECT value FROM table WHERE proc = '1' AND kind = 'a') AS a,
(SELECT value FROM table WHERE proc = '1' AND kind = 'b') AS b
并且有效,但前提是在表中我有 kind=a 和 kind=b 的两行。
但我需要,如果错过一行,我将得到一个空值:如果我查找 proc=2 我必须得到:
| v_a | v_b |
| foo | NULL|
相反,如果 b 或 a 行丢失,我的查询不会得到任何结果。
我正在使用 mysql...该怎么做?
编辑: 我可以使用 UNION 子句,但这不允许我在缺少一行时拥有 NULL 值(kind=a 或 kind=b)
i dont remember how to join a table to itself.. my table is:
| id | proc | value | kind |
| 1 | 1 | foo | a |
| 2 | 1 | bar | b |
| 3 | 2 | some | a |
And i need to retrieve the value col where proc is $proc and kind is both 'a' and 'b'.. well, i need to do have that (looking for proc = 1):
| v_a | v_b |
| foo | bar |
So i wrote this query:
SELECT
a.value AS v_a,
b.value AS v_b
FROM
(SELECT value FROM table WHERE proc = '1' AND kind = 'a') AS a,
(SELECT value FROM table WHERE proc = '1' AND kind = 'b') AS b
And works but only if in the table i have both rows for kind=a and kind=b.
But i need that if miss a row, i'll have a null value: if i look for proc=2 i must get:
| v_a | v_b |
| foo | NULL|
Instead, with my query i dont get anythong if the b or a row is missing.
Im working with mysql...How to do that?
Edit:
I could use the UNION clause, but this wont allow me to have a NULL value when one row is missing (kind=a or kind=b)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
你必须执行完全连接,以防 proc 存在于“a”但不存在于“b”,反之亦然:
编辑:第一个给出了 MS SQL Server 查询(完全连接),但显然 MYSQL 不支持它,所以我更改了它到 2 个 LEFT JOIN 的 UNION
you have to do a full join in case proc exists for 'a' but not for 'b' or vice versa:
EDIT: 1st a gave MS SQL Server query (FULL JOIN) but apparently it's not supported by MYSQL, so I changed it to a UNION of 2 LEFT JOIN
我错过了您检索 NULL 的需要。这很复杂,但似乎有效:
I missed your need to retrieve NULLs. This is complicated, but it seems to work:
在 some_id = some_other_id 上从 t1 完全外连接 t2 中选择 *
?select * from t1 full outer join t2 on some_id = some_other_id
?