两条实用sql语句

总结两条有用的sql语句。

1、一条sql语句汇总个分数段人数：

select

[0-59]=sum(casewhen score between 0 and 59 then 1 else 0 end),
[60-69]=sum(case when score between 60 and 69then 1 else 0 end),
[70-79]=sum(case when score between 70 and 79then 1 else 0 end),
[80-100]=sum(case when score between 80 and 100then 1 else 0 end)

from T group  by score

2、从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)

SELECT a.userper, a.tel,a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’) AS telyear,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’01′, a.factration)) AS JAN,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’02′, a.factration)) AS FRI,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’03′, a.factration)) AS MAR,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’04′, a.factration)) AS APR,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’05′, a.factration)) AS MAY,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’06′, a.factration)) AS JUE,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’07′, a.factration)) AS JUL,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’08′, a.factration)) AS AGU,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’09′, a.factration)) AS SEP,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’10′, a.factration)) AS OCT,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’11′, a.factration)) AS NOV,

SUM(decode(TO_CHAR(a.telfeedate,‘mm’), ’12′, a.factration)) AS DEC

FROM (SELECT a.userper,a.tel, a.standfee, b.telfeedate, b.factration

FROM TELFEESTAND a,TELFEE b

WHERE a.tel = b.telfax) a

GROUP BY a.userper,a.tel, a.standfee, TO_CHAR(a.telfeedate, ‘yyyy’)