每天,这些要求都变得更加奇怪和怪异.
我被要求组合一个查询来检测表中哪些列包含所有行的相同值.我说“这需要通过程序来完成,这样我们才能在表格的一次传递中完成,而不是N次传递.”@H_404_3@
我被推翻了.@H_404_3@
长话短说.我有这个非常简单的查询来演示这个问题.它在测试集上进行了4次传递.我正在寻找sql Magery的想法,它不涉及在每一列上添加索引,或编写程序,或者花费一整个人的生命周期来运行.@H_404_3@
叹息它需要能够在任何桌子上工作.@H_404_3@
提前感谢您的建议.@H_404_3@
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
编辑
这工作：@H_404_3@
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.@H_404_3@
而SQLFiddle demo对你来说.@H_404_3@
