ORACLE的SQL高手帮我看下错误，谢谢了

来源：百度知道 编辑：UC知道 时间：2024/05/30 21:34:01
SELECT SUM(A.JILU),
CASE A.XF_CREATEDATE WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20050101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20051231' THEN '2005'
CASE A.XF_CREATEDATE WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20060101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20061231' THEN '2006'
CASE A.XF_CREATEDATE WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20070101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20071231' THEN '2007'
CASE A.XF_CREATEDATE WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20080101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20081231' THEN '2008'
CASE A.XF_CREATEDATE WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20090101' THEN '2009'
ELSE A.XF_CREATEDATE
END 'SEASON'
FROM
(SELECT COUNT(*) JILU,XF_CREATEDATE FROM XF_PROM

首先case处用法不对
该
CASE WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20050101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20051231' THEN '2005'

再一个，else处？就是不在case里的日期你要怎么表示？
还有？为什么end 'season'?
season是什么？最后哪来的group by？
------------------------------------
晕，才发现，不用这么麻烦的吧？
select to_char(a.XF_CREATEDATE,'yyyy'),SUM(A.JILU)
(SELECT COUNT(*) JILU,XF_CREATEDATE FROM XF_PROMPOOLH
GROUP BY XF_CREATEDATE
) a
group by to_char(a.XF_CREATEDATE,'yyyy')

SELECT SUM(A.JILU),
CASE
WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20050101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20051231' THEN '2005'
WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')>'20060101' AND TO_CHAR(A.XF_CREATEDATE,'YYYYMMDD')<'20061231' THEN '2006'
WHEN TO_CHAR(A.XF_CREATEDATE,'YYYYMM