是要这个? select id ,sum(case when YN = 1 then 1 else 0 end) x from T where dt between date '2008-10-01' and date '2008-10-31' group by id order by 2;
这个题目只比入门级难一点而已 楼上的关于日期有错误 dt between date '2008-10-01' and date '2008-10-31' oracle没这样的写法的 另外不需要CASE WHEN,直接SUM不就行了
不难,这是靠你对ORACLE函数基本功的题。
select id,sum(Y/N) kqcount from 表 group by id order by sum(Y/N) desc;
select id,to_char(date,'yyyymm'),sum(decode(y/n,1,1)) from 表 where to_char(date,'yyyymm')='200810' group by to_char(date,'yyyymm') order by sum(decode(y/n,1,1)) desc
select id,to_char(date,'yyyymm'),sum(decode(y/n,1,1)) from 表 where to_char(date,'yyyymm')='200810' group by to_char(date,'yyyymm') order by 3 desc
select id, sum(yn) su from T where d between to_date('2008-10-01', 'yyyy-mm-dd') and to_date('2008-10-31', 'yyyy-mm-dd') group by id order by su
--MS_sqL select id , sum(y/n) from tb where group by id having convert(char(7),date,120)='2008-10' order by sum[y/n] desc --oracle select id,to_char(date,'yyyymm'),sum(decode(y/n,1,1)) from tb where to_char(date,'yyyymm')='200810' group by id order by sum(decode(y/n,1,1)) desc
select id
,sum(case when YN = 1 then 1 else 0 end) x
from T
where dt between date '2008-10-01' and date '2008-10-31'
group by id
order by 2;
楼上的关于日期有错误
dt between date '2008-10-01' and date '2008-10-31'
oracle没这样的写法的
另外不需要CASE WHEN,直接SUM不就行了
select id,sum(Y/N) kqcount from 表
group by id
order by sum(Y/N) desc;
呵呵,有date '2008-10-01'这种写法的。因为里面存的是1、0, 昨天没考虑到,看来是可以直接sum
from 表
where to_char(date,'yyyymm')='200810'
group by to_char(date,'yyyymm')
order by 3 desc
from T
where d between to_date('2008-10-01', 'yyyy-mm-dd') and
to_date('2008-10-31', 'yyyy-mm-dd')
group by id
order by su
--MS_sqL
select id , sum(y/n) from tb where group by id having convert(char(7),date,120)='2008-10'
order by sum[y/n] desc
--oracle
select id,to_char(date,'yyyymm'),sum(decode(y/n,1,1)) from tb
where to_char(date,'yyyymm')='200810' group by id order by sum(decode(y/n,1,1)) desc