Elsaの程式學習筆記

Table欄位說明：

Table Name：SOLD_RECORD

   Name                Type 

DATE                        CHAR(10)<or DATE>

SALES_AMT             NUMBER(10)

範例：

運算X公司之2015/01/01~2015/12/31交易日期(DATE)的每月/日實收銷售金額為何?

語法：

l   Date格式為DATE

   By 月

SELECT to_char(DATE,'YYYY/MM'), SUM(SALES_AMT) FROM SOLD_RECORD

WHERE DATE BETWEEN '2015/01/01' AND '2015/12/31' AND SALES_AMT> 0

GROUP BY to_char(DATE,'YYYY/MM')

ORDER BY DATE

   <補充>By 日

SELECT to_char(DATE,'YYYY/MM/DD'), SUM(SALES_AMT) FROM SOLD_RECORD

WHERE DATE BETWEEN '2015/01/01' AND '2015/12/31' AND SALES_AMT > 0

GROUP BY to_char(DATE,'YYYY/MM/DD')

ORDER BY DATE

l   Date格式為CHAR

   By 月

語法1：

SELECT to_char(to_date(CTRL_DATE,'yyyy/mm/dd'),'YYYY/MM'), SUM(SALES_AMT) FROM SOLD_RECORD

WHERE DATE BETWEEN '2015/01/01' AND '2015/12/31' AND SALES_AMT > 0

GROUP BY to_char(to_date(CTRL_DATE,'yyyy/mm/dd'),'YYYY/MM')

ORDER BY DATE

語法2：

SELECT SUBSTR(DATE,1,7), SUM(SALES_AMT) FROM SOLD_RECORD

WHERE DATE BETWEEN '2015/01/01' AND '2015/12/31' AND SALES_AMT > 0

GROUP BY SUBSTR(DATE,1,7)

ORDER BY SUBSTR(DATE,1,7)

   <補充>By 日

SELECT to_char(to_date(CTRL_DATE,'yyyy/mm/dd'),'YYYY/MM/DD'), SUM(SALES_AMT) FROM SOLD_RECORD

WHERE DATE BETWEEN '2015/01/01' AND '2015/12/31' AND SALES_AMT > 0

GROUP BY to_char(to_date(CTRL_DATE,'yyyy/mm/dd'),'YYYY/MM/DD')

ORDER BY DATE