我有以下 SQL：
WITH MYFILTER AS (
SELECT
CASE WHEN STATE IN ('A', 'B') then
case
when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
when (NUM/DENOM - 1.0) >= -0.3
and (NUM/DENOM - 1.0) < -0.2 then '-30% to -20%'
when (NUM/DENOM - 1.0) >= -0.2
and (NUM/DENOM - 1.0) < -0.1 then '-20% to -10%'
when (NUM/DENOM - 1.0) >= -0.1
and (NUM/DENOM - 1.0) < 0.0 then '-10% to 0%'
when (NUM/DENOM - 1.0) >= 0.0
and (NUM/DENOM - 1.0) < 0.1 then '0% to 10%'
when (NUM/DENOM - 1.0) >= 0.1
and (NUM/DENOM - 1.0) < 0.2 then '10% to 20%'
when (NUM/DENOM - 1.0) >= 0.2
and (NUM/DENOM - 1.0) < 0.3 then '20% to 30%'
when (NUM/DENOM - 1.0) >= 0.3 THEN 'At least 30%'
end
ELSE case
when (NUM/DENOM < -0.3 then 'Less than -30%'
when (NUM/DENOM >= -0.3
and (NUM/DENOM < -0.2 then '-30% to -20%'
when (NUM/DENOM >= -0.2
and (NUM/DENOM < -0.1 then '-20% to -10%'
when (NUM/DENOM >= -0.1
and (NUM/DENOM < 0.0 then '-10% to 0%'
when (NUM/DENOM >= 0.0
and (NUM/DENOM < 0.1 then '0% to 10%'
when (NUM/DENOM >= 0.1
and (NUM/DENOM < 0.2 then '10% to 20%'
when (NUM/DENOM >= 0.2
and (NUM/DENOM < 0.3 then '20% to 30%'
when (NUM/DENOM >= 0.3 THEN 'At least 30%'
end
END AS indrange
FROM MYTABLE
WHERE DENOM <> 0 AND
YEAR = 2020 AND
MONTH = 11
)
SELECT
indrange,
count (*) AS total
FROM FILTER
GROUP BY indrange
每當比率不屬于其中一個范圍時（例如，我的表中沒有 NUM/DENOM - 1 > 0.3 的行），那么我最終得到的結果不包括“至少 30%”作為值為 0 的行。相反，該行根本不存在。我將如何更改代碼以使其仍包含具有相應 0 值的“至少 30%”行？換句話說，我明白了：
INDRANGE TOTAL
Less than -30% 285
-30% to -20% 1,608
-20% to -10% 7,409
-10% to 0% 164,212
0% to 10% 169,665
10% to 20% 1
但我想要這個：
INDRANGE TOTAL
Less than -30% 285
-30% to -20% 1,608
-20% to -10% 7,409
-10% to 0% 164,212
0% to 10% 169,665
10% to 20% 1
20% to 30% 0
At Least 30% 0
我該怎么做？這是 DB2。
uj5u.com熱心網友回復：
您可以LEFT JOIN在范圍和現有 CTE 之間使用 a 。例如：
with myranges (indrange) as (
select 'Less than -30%' from sysibm.sysdummy1
union all select '-30% to -20%' from sysibm.sysdummy1
union all select '-20% to -10%' from sysibm.sysdummy1
union all select '-10% to 0%' from sysibm.sysdummy1
union all select '0% to 10%' from sysibm.sysdummy1
union all select '10% to 20%' from sysibm.sysdummy1
union all select '20% to 30%' from sysibm.sysdummy1
union all select 'At Least 30%' from sysibm.sysdummy1
),
myfilter as (
-- here add you existing CTE
)
select r.indrange, count(f.indrange) AS total
from myranges r
left join myfilter f on f.indrange = r.indrange
group by r.indrange
uj5u.com熱心網友回復：
未經測驗：但根據我之前的評論，類似這樣的事情。
- 添加了一個 MyRanges cte 列出每個范圍 1 次，從而確保每個范圍都包含在最終查詢中。
- 在最終查詢中使用 Union all 將其與來自 MyFilter 的結果相結合
- 從 count(*) 結果中減去 1，因為我們將 myRanges union 中的結果增加了 1
.
WITH MYRANGES AS(
SELECT 'Less than -30%' as INDRANGE FROM DUAL UNION ALL
SELECT '-30% to -20%' as INDRANGE FROM DUAL UNION ALL
SELECT '-20% to -10%' as INDRANGE FROM DUAL UNION ALL
SELECT '-10% to 0%' as INDRANGE FROM DUAL UNION ALL
SELECT '0% to 10%' as INDRANGE FROM DUAL UNION ALL
SELECT '10% to 20%' as INDRANGE FROM DUAL UNION ALL
SELECT '20% to 30%' as INDRANGE FROM DUAL UNION ALL
SELECT 'At Least 30%' as INDRANGE FROM DUAL),
MYFILTER AS (SELECT
CASE WHEN STATE IN ('A', 'B') then
case
when (NUM/DENOM - 1.0) < -0.3 then 'Less than -30%'
when (NUM/DENOM - 1.0) >= -0.3
and (NUM/DENOM - 1.0) < -0.2 then '-30% to -20%'
when (NUM/DENOM - 1.0) >= -0.2
and (NUM/DENOM - 1.0) < -0.1 then '-20% to -10%'
when (NUM/DENOM - 1.0) >= -0.1
and (NUM/DENOM - 1.0) < 0.0 then '-10% to 0%'
when (NUM/DENOM - 1.0) >= 0.0
and (NUM/DENOM - 1.0) < 0.1 then '0% to 10%'
when (NUM/DENOM - 1.0) >= 0.1
and (NUM/DENOM - 1.0) < 0.2 then '10% to 20%'
when (NUM/DENOM - 1.0) >= 0.2
and (NUM/DENOM - 1.0) < 0.3 then '20% to 30%'
when (NUM/DENOM - 1.0) >= 0.3 THEN 'At least 30%'
end
ELSE case
when (NUM/DENOM < -0.3 then 'Less than -30%'
when (NUM/DENOM >= -0.3
and (NUM/DENOM < -0.2 then '-30% to -20%'
when (NUM/DENOM >= -0.2
and (NUM/DENOM < -0.1 then '-20% to -10%'
when (NUM/DENOM >= -0.1
and (NUM/DENOM < 0.0 then '-10% to 0%'
when (NUM/DENOM >= 0.0
and (NUM/DENOM < 0.1 then '0% to 10%'
when (NUM/DENOM >= 0.1
and (NUM/DENOM < 0.2 then '10% to 20%'
when (NUM/DENOM >= 0.2
and (NUM/DENOM < 0.3 then '20% to 30%'
when (NUM/DENOM >= 0.3 THEN 'At least 30%'
end
END AS indrange
FROM MYTABLE
WHERE DENOM <> 0 AND
YEAR = 2020 AND
MONTH = 11
),
SELECT indrange, count (*)-1 AS total
FROM (SELECT * FROM MyFilter UNION ALL
SELECT * FROM MyRanges) as MyFilterandAllRanges
GROUP BY indrange
好吧，現在我已經做了一些事情，我已經考慮過了，我可以做得更好......
所以將最終查詢修改為.... 我們不必弄亂數字和數學。我們基本上是采用范圍的“主串列”并將其外部連接到我們的總數中，以便始終包括所有范圍，并且只有那些匹配的范圍才會顯示總數。我們使用合并來處理一些可能不存在的事實并替換所需的 0。
SELECT MyRanges.indrange, coalesce(total,0) as total
FROM MyRanges
LEFT JOIN (SELECT count(*) total, Indrange
FROM MyFilter
GROUP BY indrange) as sub
on MyRanges.indrange = sub.indrange
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/383810.html