sales我正在嘗試回傳 2016 年的 every month, everyquarter的總數year。我想在第一行顯示年銷售額month，而不是在其他行。另外，我想在每個quarter的第一個上顯示銷售額，而不是在其他的上。monthquarter
為了進一步解釋這一點，這就是我想要實作的目標：
MONTH MONTH_SALES QUARTER_SALES YEAR_SALES
1 2183 5917 12505
2 1712 - -
3 1972 - -
4 2230 6588 -
5 2250 - -
6 2108 - -
到目前為止，這是我的 SQL 查詢：
SELECT
Time.month,
SUM(Sales.sales) AS MONTH_SALES, -- display monthly sales.
CASE
WHEN MOD(Time.month, 3) = 1 THEN ( -- first month of quarter
SELECT
SUM(Sales.sales)
FROM
Sales,
Time
WHERE
Sales.Time_id = Time.Time_id
AND Time.year = 2016
GROUP BY
Time.quarter
FETCH FIRST 1 ROW ONLY
)
END AS QUARTER_SALES,
CASE
WHEN Time.month = 1 THEN ( -- display annual sales.
SELECT
SUM(Sales.sales)
FROM
Sales,
Time
WHERE
Sales.Time_id = Time.Time_id
AND Time.year = 2016
GROUP BY
Time.year
)
END AS YEAR_SALES
FROM
Sales,
Time
WHERE
Sales.Time_id = Time.Time_id
AND Time.year = 2016
GROUP BY
Time.month
ORDER BY
Time.month
我幾乎得到了想要的輸出，但是我在第一個月和第四個月的季度銷售額中得到了相同的重復 6588 值（因為我正在獲取來自第一季度的第一行）。
MONTH MONTH_SALES QUARTER_SALES YEAR_SALES
1 2183 6588 12505
2 1712 - -
3 1972 - -
4 2230 6588 -
5 2250 - -
6 2108 - -
我什至嘗試輸入，WHERE Time.quarter = ((Time.month * 4) / 12)但month外部查詢的值沒有在子查詢中傳遞。
不幸的是，我沒有足夠的CASE WHEN運算式經驗來知道如何通過月份行。任何提示都會很棒。
uj5u.com熱心網友回復：
這個怎么樣？
樣本資料：
SQL> with
2 time (time_id, month, quarter, year) as
3 (select 1, 1, 1, 2016 from dual union all
4 select 2, 2, 1, 2016 from dual union all
5 select 3, 3, 1, 2016 from dual union all
6 select 4, 5, 2, 2016 from dual union all
7 select 5, 7, 3, 2016 from dual union all
8 select 6, 8, 3, 2016 from dual union all
9 select 7, 9, 3, 2016 from dual union all
10 select 8, 10, 4, 2016 from dual union all
11 select 9, 11, 4, 2016 from dual
12 ),
13 sales (time_id, sales) as
14 (select 1, 100 from dual union all
15 select 1, 100 from dual union all
16 select 2, 200 from dual union all
17 select 3, 300 from dual union all
18 select 4, 400 from dual union all
19 select 5, 500 from dual union all
20 select 6, 600 from dual union all
21 select 7, 700 from dual union all
22 select 8, 800 from dual union all
23 select 9, 900 from dual
24 ),
查詢從這里開始；它以分析形式使用sum聚合；partition by子句說明要計算什么。row_number，類似地，對每個季度/年度中的行進行排序 - 稍后在 CASE 運算式中使用它來決定是否顯示季度/年度總計。
25 temp as
26 (select t.month, t.quarter, t.year, sum(s.sales) month_sales
27 from time t join sales s on s.time_id = t.time_id
28 where t.year = 2016
29 group by t.month, t.quarter, t.year
30 ),
31 temp2 as
32 (select month, quarter, month_sales,
33 sum(month_sales) over (partition by quarter) quarter_sales,
34 sum(month_sales) over (partition by year ) year_sales,
35 row_number() over (partition by quarter order by quarter) rnq,
36 row_number() over (partition by year order by null) rny
37 from temp
38 )
39 select month,
40 month_sales
41 case when rnq = 1 then quarter_sales end month_sales,
42 case when rny = 1 then year_sales end year_sales
43 from temp2
44 order by month;
MONTH MONTH_SALES QUARTER_SALES YEAR_SALES
---------- ---------- ----------- ----------
1 200 700 4600
2 200
3 300
4 400 1500
5 500
6 600
7 700 2400
8 800
9 900
9 rows selected.
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/430045.html