select name from table where name=(select name from table where month between 200301 and 200309 group by name having count(name)=9)
between之间的200301和200309你自己改一改,我不知道你的日期格式
select * from table where name=(select name from table where month between 200301 and 200309 group by name having count(name)=9)
考虑到一个NAME在同一个月份可能有两次以上的处罚(两条记录) 更改如下 select * from table where name=(select name from table where month between 200301 and 200309 group by name having count(distinct month)=9);
select name, sum(case to_char(month,'mm') when '01' then 1 else 0 end) month1, sum(case to_char(month,'mm') when '02' then 1 else 0 end) month2, sum(case to_char(month,'mm') when '03' then 1 else 0 end) month3, sum(case to_char(month,'mm') when '05' then 1 else 0 end) month4, sum(case to_char(month,'mm') when '05' then 1 else 0 end) month5, sum(case to_char(month,'mm') when '06' then 1 else 0 end) month6, sum(case to_char(month,'mm') when '07' then 1 else 0 end) month7, sum(case to_char(month,'mm') when '08' then 1 else 0 end) month8, sum(case to_char(month,'mm') when '09' then 1 else 0 end) month9, sum(case to_char(month,'mm') when '10' then 1 else 0 end) month10, sum(case to_char(month,'mm') when '11' then 1 else 0 end) month11, sum(case to_char(month,'mm') when '12' then 1 else 0 end) month12 from table group by name; 上一个得出每个用户各月的处罚汇总.如果某个月没有处罚,则对应的列值为0.要得到某几个月都没有处罚, 则各月之和为0 (month1 + ... + monthn = 0) 要得到某几个月曾经有过处罚,则各月之和>0 (month1 + ... + monthn > 0) 要得到某几个月都有处罚, 则各月之乘积不为0(month1 * ... * monthn > 0)
方法一: select name from table where month between 200301 and 200309 group by name having count(1)=9 方法二: select name from (select name,rank() over(order by month) rk from table where month between 200301 and 200309) where rk=9
select name from table where month = all(select distinct month from table) group by name
where name=(select name from table
where month between 200301 and 200309
group by name having count(name)=9)
where name=(select name from table
where month between 200301 and 200309
group by name having count(name)=9)
更改如下
select * from table
where name=(select name from table
where month between 200301 and 200309
group by name having count(distinct month)=9);
sum(case to_char(month,'mm') when '01' then 1 else 0 end) month1,
sum(case to_char(month,'mm') when '02' then 1 else 0 end) month2,
sum(case to_char(month,'mm') when '03' then 1 else 0 end) month3,
sum(case to_char(month,'mm') when '05' then 1 else 0 end) month4,
sum(case to_char(month,'mm') when '05' then 1 else 0 end) month5,
sum(case to_char(month,'mm') when '06' then 1 else 0 end) month6,
sum(case to_char(month,'mm') when '07' then 1 else 0 end) month7,
sum(case to_char(month,'mm') when '08' then 1 else 0 end) month8,
sum(case to_char(month,'mm') when '09' then 1 else 0 end) month9,
sum(case to_char(month,'mm') when '10' then 1 else 0 end) month10,
sum(case to_char(month,'mm') when '11' then 1 else 0 end) month11,
sum(case to_char(month,'mm') when '12' then 1 else 0 end) month12
from table
group by name;
上一个得出每个用户各月的处罚汇总.如果某个月没有处罚,则对应的列值为0.要得到某几个月都没有处罚, 则各月之和为0 (month1 + ... + monthn = 0)
要得到某几个月曾经有过处罚,则各月之和>0 (month1 + ... + monthn > 0)
要得到某几个月都有处罚, 则各月之乘积不为0(month1 * ... * monthn > 0)
select name from table where month between 200301 and 200309 group by name having count(1)=9
方法二:
select name from (select name,rank() over(order by month) rk from table where month between 200301 and 200309) where rk=9