选择具有的数据

Question

我有三个LEFT JOIN编辑的表。在这种情况下，我需要使用COALESCE(ID, ID1)用ID1替换缺失的ID。

目标是只获取ID及其所有相关数据条目，给定OPS列中的值"w"。

我尝试使用EXISTS子句，但它只返回列OPS值为"w"的行，而不返回与同一ID:相关的其他行

SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3 ON COALESCE(t3.id, t3.id1) = t1.id
WHERE EXISTS (SELECT 1 
FROM table3 
WHERE id = t1.id
AND (t3.ops = 'w'))

现在我试图弄清楚为什么我的EXISTS子句不能正常工作。

数据：
db<gt；小提琴

这是我想要用代码产生的结果：

qweqw[/tr>wqwBwqww

id	年龄	操作
1	23	A
1	23	A
1	23	A
1	23		B
1	23		B
1	23	B	e
1		23	C	q
1	23	C
1		23	C	e
2	25	A
2	25	A
2	25	q
2	25	B
4	43	A
4	43	A
4	43	B	q
4	43	B

如果我理解正确，我建议使用窗口函数而不是exist来检查每个组是否有操作w:

SELECT *
FROM (
SELECT t1.id AS t1id, 
t1.age AS t1age, 
t2.id AS t2id, 
t2.id1 AS t2id1, 
t2.operation AS t2operation, 
t3.id AS t3id, 
t3.id1 AS t3id1, 
t3.ops AS t3ops,
MAX(CASE WHEN t3.ops = 'w' then 1 else 0 end) OVER(PARTITION BY t1.id) has_ops_w
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3 ON COALESCE(t3.id, t3.id1) = t1.id
) t
WHERE has_ops_w = 1

在你的数据库Fiddle:

has_ops_w

t1id		t3ops
1	1
1	1
1	1
1	1
1		1
1		1
1	1
1		1
1		1
2	1
2	1
2	1
2	1
4	1
4	1
4	1
4		1

GMB指定的窗口函数可以工作，但我认为使用EXISTS子句也有一些混乱。

SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3 ON COALESCE(t3.id, t3.id1) = t1.id
WHERE EXISTS (
SELECT 1 
FROM table3 inner_t3
WHERE COALESCE(inner_t3.id, inner_t3.id1) = t1.id -- your join above wasn't 
-- on ID alone. This one 
-- shouldn't be either
AND inner_t3.ops = 'w'  -- Make sure you have proper reference to inner 
-- table using alias.
)

现在我明白了
这里又是EXISTS子句，其中包含@Thom A给出的提高性能的建议
此外，@oglester还就名称惯例提出了一些建议，这对我们帮助很大。

SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2 
ON t2.id = t1.id OR (t2.id IS NULL AND t2.id1 = t1.id)
LEFT JOIN table3 AS t3 
ON t3.id = t1.id OR (t3.id IS NULL AND t3.id1 = t1.id)
WHERE EXISTS (
SELECT 1 
FROM table3 AS inner_t3 
WHERE (inner_t3.id = t1.id OR (inner_t3.id IS NULL AND inner_t3.id1 = t1.id))
AND inner_t3.ops = 'w'
);