sql – 检测oracle表中每行中具有相同值的所有列

每天,这些要求都变得更加奇怪和怪异.

我被要求组合一个查询来检测表中哪些列包含所有行的相同值.我说“这需要通过程序来完成,这样我们才能在表格的一次传递中完成,而不是N次传递.”

我被推翻了.

长话短说.我有这个非常简单的查询来演示这个问题.它在测试集上进行了4次传递.我正在寻找SQL Magery的想法,它不涉及在每一列上添加索引,或编写程序,或者花费一整个人的生命周期来运行.

叹息它需要能够在任何桌子上工作.

提前感谢您的建议.

WITH TEST_CASE AS
(
    SELECT 'X' A,5 B,'FRI' C,NULL D FROM DUAL UNION ALL
    SELECT 'X' A,3 B,7 B,'TUE' C,NULL D FROM DUAL 
),KOUNTS AS 
(
    SELECT SQRT(COUNT(*)) S,'Column A' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P,TEST_CASE Q
    WHERE P.A = Q.A OR (P.A IS NULL AND Q.A IS NULL)

    UNION ALL

    SELECT SQRT(COUNT(*)) S,'Column B' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P,TEST_CASE Q
    WHERE P.B = Q.B OR (P.B IS NULL AND Q.B IS NULL)

    UNION ALL

    SELECT SQRT(COUNT(*)) S,'Column C' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P,TEST_CASE Q
    WHERE P.C = Q.C OR (P.C IS NULL AND Q.C IS NULL)

    UNION ALL

    SELECT SQRT(COUNT(*)) S,'Column D' COLUMNS_WITH_SINGLE_VALUES
    FROM TEST_CASE P,TEST_CASE Q
    WHERE P.D = Q.D OR (P.D IS NULL AND Q.D IS NULL)
)
SELECT COLUMNS_WITH_SINGLE_VALUES
FROM KOUNTS
WHERE S = (SELECT COUNT(*) FROM TEST_CASE)

解决方法

WITH 
TEST_CASE AS
(
    SELECT 'X' A,NULL D FROM DUAL 
)
select case when min(A) = max(A) THEN 'A'
            when min(B) = max(B) THEN 'B'
            when min(C) = max(C) THEN 'C'
            when min(D) = max(D) THEN 'D'
            else 'No one'
       end 
from TEST_CASE

编辑
这工作：

WITH 
TEST_CASE AS
(
    SELECT 'X' A,NULL D FROM DUAL 
)
select case when min(nvl(A,0)) = max(nvl(A,0)) THEN 'A ' end ||
       case when min(nvl(B,0)) = max(nvl(B,0)) THEN 'B ' end ||
       case when min(nvl(C,0)) = max(nvl(C,0)) THEN 'C ' end ||
       case when min(nvl(D,0)) = max(nvl(D,0)) THEN 'D ' end c

from TEST_CASE

额外：我还添加了空值的检查,因此现在的结果是：A和D.

而SQLFiddle demo对你来说.

（编辑：ASP站长网）