解決查詢結構問題-有解無憂

在以下查詢中，我必須同時使用頂部的注釋行，同時我必須以這種格式（按月份分組）保留圖片中的這兩列。

https://i.stack.imgur.com/3uabV.png

select  --REPLACE(SUBSTR(p.prod_desc,20,200),'size XXXL','size 14.00') ||':PRODUCT_ID='||p.PROD_ID as "PRODUCT_DESCRIPTION",
CASE WHEN t.CALENDAR_WEEK_NUMBER = 13 THEN 'First week'
         WHEN t.CALENDAR_WEEK_NUMBER = 14 THEN 'Second week'
         WHEN t.CALENDAR_WEEK_NUMBER = 15 THEN 'Third week'
         WHEN t.CALENDAR_WEEK_NUMBER = 16 THEN 'Fourth week'
         WHEN t.CALENDAR_WEEK_NUMBER = 17 THEN 'Fifth week'
    END as "WEEK_IN_MONTH",
        sum(s.AMOUNT_SOLD) as TOTAL_AMOUNT_SOLD   
from TIMES t join SALES s
    on t.TIME_ID = s.TIME_ID
  JOIN PRODUCTS p
    on p.PROD_ID = s.PROD_ID
where t.CALENDAR_MONTH_DESC = '2000-04'
      and p.PROD_ID in (300,10,540)
group by t.CALENDAR_WEEK_NUMBER
HAVING sum(s.AMOUNT_SOLD) > 0;

uj5u.com熱心網友回復：

如果我對您的理解正確，您的問題是-如果您取消注釋該replace代碼段-您必須將其包含在group by子句中。

字面上地：

  SELECT    REPLACE (SUBSTR (p.prod_desc, 20, 200), 'size XXXL', 'size 14.00')
         || ':PRODUCT_ID='
         || p.prod_id AS "PRODUCT_DESCRIPTION",
         CASE
            WHEN t.calendar_week_number = 13 THEN 'First week'
            WHEN t.calendar_week_number = 14 THEN 'Second week'
            WHEN t.calendar_week_number = 15 THEN 'Third week'
            WHEN t.calendar_week_number = 16 THEN 'Fourth week'
            WHEN t.calendar_week_number = 17 THEN 'Fifth week'
         END AS "WEEK_IN_MONTH",
         SUM (s.amount_sold) AS total_amount_sold
    FROM times t
         JOIN sales s ON t.time_id = s.time_id
         JOIN products p ON p.prod_id = s.prod_id
   WHERE     t.calendar_month_desc = '2000-04'
         AND p.prod_id IN (300, 10, 540)
GROUP BY t.calendar_week_number,
            REPLACE (SUBSTR (p.prod_desc, 20, 200),
                     'size XXXL',
                     'size 14.00')
         || ':PRODUCT_ID='
         || p.prod_id
  HAVING SUM (s.amount_sold) > 0;

另一種選擇是聚合replace代碼部分，這樣就不需要將它放在group by：

  SELECT MAX (
               REPLACE (SUBSTR (p.prod_desc, 20, 200),
                        'size XXXL',
                        'size 14.00')
            || ':PRODUCT_ID='
            || p.prod_id) AS "PRODUCT_DESCRIPTION",
         CASE
            WHEN t.calendar_week_number = 13 THEN 'First week'
            WHEN t.calendar_week_number = 14 THEN 'Second week'
            WHEN t.calendar_week_number = 15 THEN 'Third week'
            WHEN t.calendar_week_number = 16 THEN 'Fourth week'
            WHEN t.calendar_week_number = 17 THEN 'Fifth week'
         END AS "WEEK_IN_MONTH",
         SUM (s.amount_sold) AS total_amount_sold
    FROM times t
         JOIN sales s ON t.time_id = s.time_id
         JOIN products p ON p.prod_id = s.prod_id
   WHERE     t.calendar_month_desc = '2000-04'
         AND p.prod_id IN (300, 10, 540)
GROUP BY t.calendar_week_number
  HAVING SUM (s.amount_sold) > 0;

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/522721.html

標籤：sql甲骨文oracle11g甲骨文10g

上一篇：從包含字串的列中提取一個數字

下一篇：從AstroJS[plugin:astro]中的頁面未定義位置