今天看了一下Case语句,蛮有用的,比decode强大.
----基金净值比例
SELECT fnwd.CALCULATE_DATE,COUNT (CASE
WHEN fnwd.NET_WORTH < 1
THEN 1
END) 小于1,
COUNT (CASE
WHEN fnwd.NET_WORTH < 2 AND fnwd.NET_WORTH >= 1
THEN 1
END
) 小于2大于等于1,
COUNT (CASE
WHEN fnwd.NET_WORTH < 3 AND fnwd.NET_WORTH >= 2
THEN 1
END
) 小于3大于等于2,
COUNT (CASE
WHEN fnwd.NET_WORTH < 4 AND fnwd.NET_WORTH >= 3
THEN 1
END
) 小于4大于等于3,
COUNT (CASE
WHEN fnwd.NET_WORTH < 5 AND fnwd.NET_WORTH >= 4
THEN 1
END
) 小于5大于等于4,
COUNT (CASE
WHEN fnwd.NET_WORTH >= 5
THEN 1
END) 大于等于5
FROM T_FUND_NET_WORTH_DAILY fnwd
where fnwd.CALCULATE_DATE between TO_DATE ('2007-05-31', 'YYYY-MM-DD') and TO_DATE ('2007-07-03', 'YYYY-MM-DD')
group by fnwd.CALCULATE_DATE
case语句还可以嵌套
select (case when qty_less6months < 0 and qty_6to12months < 0 then
(case when season_code in ('0', '1', '2', '3', '4') then 'value is negative'
else 'No stock'
end)
when qty_1to2years < 0 and qty_2to3years < 0 then
(case when season_code in ('A', 'B', 'C', 'D', 'E') then 'value is negative'
else 'No stock'
end)
else 'Stock Available'
end) stock_check
from jnc_lots_ageing_mexx_asof
where rownum < 20
and qty_less6months < 0 and qty_6to12months < 0
转自:http://keke-wanwei.javaeye.com/blog/99173