oracle SQL:如何在列中查找不同的值?

lg40wkob  于 2023-04-29  发布在  Oracle
关注(0)|答案(1)|浏览(197)

我有下面的Oracle SQL表,其中product_id 101和103分别在attr1attr3列中具有不同的值。
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
;
368yc8dk

368yc8dk1#

您使用的聚合是正确的,但请正确使用它:

select product_id, 'issue with ' || regexp_replace(
    case when count(distinct attr1) > 1 then 'attr1, ' end ||
    case when count(distinct attr2) > 1 then 'attr2, ' end ||
    case when count(distinct attr3) > 1 then 'attr3, ' end, ', $', '')
from t
group by product_id
having count(distinct attr1) > 1
or     count(distinct attr2) > 1
or     count(distinct attr3) > 1

相关问题