数据提取日

Question

我有一个带有字段的销售表: product，date，qty。我需要提取这样的每日格式的产品明智的销售

Product 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 .............. 31 Total Sale
Rice    X X X X X X X X X XX XX XX XX XX XX XX............... XX    
Tea     X X X X X X X X X XX XX XX XX XX XX XX............... XX

有谁知道怎么做吗。

Answer 1

您可以在oracle中使用以下语句

SELECT 
  product
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '01' THEN qty ELSE 0 END ) as 1
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '02' THEN qty ELSE 0 END ) as 2
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '03' THEN qty ELSE 0 END ) as 3
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '04' THEN qty ELSE 0 END ) as 4
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '05' THEN qty ELSE 0 END ) as 5
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '06' THEN qty ELSE 0 END ) as 6
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '07' THEN qty ELSE 0 END ) as 7
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '08' THEN qty ELSE 0 END ) as 8
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '09' THEN qty ELSE 0 END ) as 9
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '10' THEN qty ELSE 0 END ) as 10
  sum( CASE  WHEN TO_CHAR(date, 'dd') = '11' THEN qty ELSE 0 END ) as 11
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '12' THEN qty ELSE 0 END ) as 12
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '13' THEN qty ELSE 0 END ) as 13
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '14' THEN qty ELSE 0 END ) as 14
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '15' THEN qty ELSE 0 END ) as 15
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '16' THEN qty ELSE 0 END ) as 16
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '17' THEN qty ELSE 0 END ) as 17
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '18' THEN qty ELSE 0 END ) as 18
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '19' THEN qty ELSE 0 END ) as 19
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '20' THEN qty ELSE 0 END ) as 20
  sum( CASE  WHEN TO_CHAR(date, 'dd') = '21' THEN qty ELSE 0 END ) as 21
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '22' THEN qty ELSE 0 END ) as 22
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '23' THEN qty ELSE 0 END ) as 23
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '24' THEN qty ELSE 0 END ) as 24
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '25' THEN qty ELSE 0 END ) as 25
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '26' THEN qty ELSE 0 END ) as 26
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '27' THEN qty ELSE 0 END ) as 27
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '28' THEN qty ELSE 0 END ) as 28
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '29' THEN qty ELSE 0 END ) as 29
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '30' THEN qty ELSE 0 END ) as 30
, sum( CASE  WHEN TO_CHAR(date, 'dd') = '31' THEN qty ELSE 0 END ) as 31
, sum(qty)                                                         as total
FROM 
table
group by product
;

Answer 2

你必须使用PIVOT，我在这里只放了6天，包括31天

WITH base AS (
SELECT product, day(date) AS DAY, sum(qty) AS tot
FROM product GROUP BY product, date)
SELECT product, [1], [2], [3], [4], [5], [6]
FROM base pivot(sum(tot) FOR DAY IN ([1],[2],[3],[4],[5],[6])) as tot;

Answer 3

这是你想要的吗？

SELECT PRODUCT, T_DATE,SUM(QTY)
FROM (SELECT PRODUCT, TRUNC(DATE) T_DATE,QTY
       FROM TABLE_NAME) 
GROUP BY PRODUCT, T_DATE
ORDER BY PRODUCT