我有下面的Oracle SQL表,其中product_id
101和103分别在attr1
和attr3
列中具有不同的值。data
| PRODUCT_ID | ATTR1 | ATTR2 | ATTR3 |
|------------|-------|-------|-------|
| 101 | a | x | z |
| 101 | a | x | z |
| 101 | aa | x | z |
| 102 | b | y | z |
| 102 | b | y | z |
| 103 | c | z | z |
| 103 | c | z | zz |
假设有7000行和30列要检查此逻辑,那么获得如下所示突出显示差异的输出查询的最有效方法是什么?output
| PRODUCT_ID | DESCR |
|------------|------------------|
| 101 | Issue with attr1 |
| 103 | Issue with attr3 |
query
WITH data AS (
SELECT 101 product_id, 'a' attr1, 'x' attr2, 'z' attr3 FROM dual
UNION ALL
SELECT 101 product_id, 'a' attr, 'x' attr2, 'z' attr3 FROM dual
UNION ALL
SELECT 101 product_id, 'aa' attr, 'x' attr2, 'z' attr3 FROM dual
UNION ALL
SELECT 102 product_id, 'b' attr, 'y' attr2, 'z' attr3 FROM dual
UNION ALL
SELECT 102 product_id, 'b' attr, 'y' attr2, 'z' attr3 FROM dual
UNION ALL
SELECT 103 product_id, 'c' attr, 'z' attr2, 'z' attr3 FROM dual
UNION ALL
SELECT 103 product_id, 'c' attr, 'z' attr2, 'zz' attr3 FROM dual
)
SELECT product_id, 'Issue with attr1' descr FROM data GROUP BY product_id HAVING COUNT(DISTINCT attr1) > 1
UNION
SELECT product_id, 'Issue with attr2' descr FROM data GROUP BY product_id HAVING COUNT(DISTINCT attr2) > 1
UNION
SELECT product_id, 'Issue with attr3' descr FROM data GROUP BY product_id HAVING COUNT(DISTINCT attr3) > 1
;
1条答案
按热度按时间368yc8dk1#
您使用的聚合是正确的,但请正确使用它: