如何從下面的查詢中獲取具有null值的行address_1。
SELECT id, address_1, address_2, address_3 FROM table GROUP BY address_2, address_3 HAVING COUNT(id) > 1;
在所有分組中只有一行具有null值address_1。
id address_1 address_2 address_3
-----------------------------------
1 7 Orange High County CA 10020
2 High County CA 10020
3 20 Orange High County CA 10020
4 True Avenue VA 24019
5 100 Apple True Avenue VA 24019
6 123 Apple True Avenue VA 24019
7 Long Island NY 10000
...
我想獲取第 2 行和第 4 行的 ID（相應地更新或洗掉它）。
uj5u.com熱心網友回復：
對于此示例資料，您可以使用條件聚合：
SELECT MAX(CASE WHEN address_1 IS NULL THEN id END) id
FROM tablename
GROUP BY address_2, address_3
HAVING COUNT(address_1) > 0 AND COUNT(address_1) < COUNT(*);
請參閱演示。
uj5u.com熱心網友回復：
要找到這些id值，您可以使用：
SELECT id
FROM yourTable t1
WHERE address_1 IS NULL AND
EXISTS (SELECT 1 FROM yourTable t2
WHERE t2.address_2 = t1.address_2 AND
t2.address_3 = t1.address_3 AND
t2.id <> t1.id AND
t2.id IS NOT NULL);
演示
uj5u.com熱心網友回復：
SELECT id, address_1, address_2, address_3
FROM table
WHERE address_1 IS NULL
group by address_2,address_3;
如果address_1每組中只有一行為空值，則無需分組。
那是你需要的嗎？還是我沒理解你？
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/362932.html