SQL：如何處理NULL和PARTITIONBY？

我有個問題，如果你不介意的話。所以假設我在這里有這樣一張表——產品（2000 年按季度銷售的數量，同一產品和季度只有多個條目（日期不同））：

我想重新介紹它如下：

我決定用磁區來做（因為它不是那么簡單，同一產品在同一季度有不同的行，但銷售量不同，這就是為什么它是 sum(amount_sold)，但你明白了，我希望） ：

WITH quater_sales as(
SELECT DISTINCT pro.product, pro.quarter, to_char (sum(pro.amount_sold) OVER (PARTITION BY pro.product, pro.quarter)) AS quater
FROM products pro
ORDER BY pro.pro.product)
SELECT quater_sales.prod_product, quater_sales.quater AS "Q1", qu2.quater AS "Q2", qu3.quater AS "Q3", qu4.quater AS "Q4"
FROM quater_sales
 JOIN quater_sales qu2 ON quater_sales.prod_subcategory=qu2.prod_subcategory
 JOIN quater_sales qu3 ON quater_sales.prod_subcategory=qu3.prod_subcategory
 JOIN quater_sales qu4 ON quater_sales.prod_subcategory=qu4.prod_subcategory
WHERE quater_sales.calendar_quarter_number=1 and qu2.calendar_quarter_number=2 and qu3.calendar_quarter_number=3 and qu4.calendar_quarter_number=4

問題在于磁區（或者可能是選擇的條件），即在所有 4 個季度中都沒有售出的產品被丟棄了。我最終得到的基本上是這樣的：

那么如何讓“裙子”也出現在那里呢？我有點堅持這一點。

uj5u.com熱心網友回復：

您是否考慮過使用PIVOT宣告？

WITH
    quarter_sales (product, quarter, amount_sold)
    AS
        (SELECT 'Jeans', 1, 20 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 2, 40 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 3, 60 FROM DUAL
         UNION ALL
         SELECT 'Jeans', 4, 5 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 1, 10 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 2, 5 FROM DUAL
         UNION ALL
         SELECT 'Skirt', 3, 30 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 1, 15 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 2, 40 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 3, 60 FROM DUAL
         UNION ALL
         SELECT 'Blouse', 4, 15 FROM DUAL)
SELECT *
  FROM (SELECT *
          FROM quarter_sales qs)
       PIVOT (SUM (amount_sold)
             FOR quarter
             IN (1 AS quarter1, 2 AS quarter2, 3 AS quarter3, 4 AS quarter4));


   PRODUCT    QUARTER1    QUARTER2    QUARTER3    QUARTER4
__________ ___________ ___________ ___________ ___________
Blouse              15          40          60          15
Jeans               20          40          60           5
Skirt               10           5          30

uj5u.com熱心網友回復：

嘗試樞軸。這就是你在 tsql 中的旋轉方式

declare @tmp as table(product varchar(20),quarter int,[amount sold] int);

insert into @tmp values
('Jeans',   1,  20)
,('Jeans',  2,  40)
,('Jeans',  3,  60)
,('Jeans',  4,  5)
,('Skirt',  1,  10)
,('Skirt',  2,  5)
,('Skirt',  3,  30)
,('Blouse', 1,  15)
,('Blouse', 2,  40)
,('Blouse', 3,  60)
,('Blouse', 4,  15)


    select  product, [1] as quarter1,[2] as quarter2,[3] as quarter3,[4] as quarter4
from
(
select product,quarter,[amount sold] from @tmp)p
pivot
(
sum([amount sold])
for quarter in([1],[2],[3],[4])
) as pvt

輸出：

product quarter1    quarter2    quarter3    quarter4
Blouse  15          40          60          15
Jeans   20          40          60          5
Skirt   10          5           30          NULL

標籤：sql 甲骨文 窗函数

上一篇：SQl-如何將Total列和行添加到我的sql結果集中

下一篇：在不同版本的OracleDB上運行的SQL查詢