sql分组查询,group by count(*) having 数据重复
单条语句查询
(select org1.name as orgname, COUNT(DISTINCT employeeid) from t_hr_emponduty as em1
left join t_hr_org as org1 on org1.id=em1.orgid where employeeid not in('','NULL')
and ondutydate BETWEEN CONVERT(datetime,'2011-11-1') and CONVERT(datetime,'2011-11-30')
group by org1.name)
结果:
name number1
1 1
2 3
3 1
拼了三条语句:
select org.name,
(select COUNT(DISTINCT employeeid) from t_hr_emponduty as em1
left join t_hr_org as org1 on org1.id=em1.orgid where employeeid not in('','NULL')
and ondutydate BETWEEN CONVERT(datetime,'2011-11-1') and CONVERT(datetime,'2011-11-30')
) as number1,
(select COUNT(DISTINCT employeeid) from t_hr_emponduty as em2
left join t_hr_org as org2 on org2.id=em2.orgid left join t_hr_employee as ep2 on ep2.id=em2.employeeid where employeeid not in('','NULL')
and em2.positivedate BETWEEN CONVERT(datetime,'2011-11-1') and CONVERT(datetime,'2011-11-30')
and ep2.matriculatesource='402848b62569c53901256d0d8c332110' ) as number2,
(select COUNT(DISTINCT employeeid) from t_hr_emponduty as em3
left join t_hr_org as org3 on org3.id=em3.orgid left join t_hr_employee as ep3 on ep3.id=em3.employeeid where employeeid not in('','NULL')
and em3.positivedate BETWEEN CONVERT(datetime,'2011-11-1') and CONVERT(datetime,'2011-11-30')
and ep3.matriculatesource='402848b62569c53901256d0d8c3321e9' ) as number3
from t_hr_org as org,t_hr_emponduty as em where
org.id=em.orgid
group by org.name having count(*)>1
正确的查询结果应为:
name number1 number2 number3
xxx 6 0 0
aaaa 0 0 0
。。。。。。。。。。。。。。。。。。。。
可是照我上面的语句查询结果为
name number1 number2 number3
xxx 6 0 0
aaaa 6 0 0
cccc 6 0 0
dddd 6 0 0
但是我查询出的每行number都要对应一个name,所以就只有在外层匹配org.name了,后面还有十几条查询number的语句。每个number都是所属一个org.name的一个汇总,现在只是分了很多种状态把它查询出来,再对应到所属的org.name
[解决办法]
這樣試試
select org.name,( select COUNT(DISTINCT employeeid) from t_hr_emponduty as em1 left join t_hr_org as org1 on org1.id=em1.orgid where employeeid <>'' and employeeid is not null and ondutydate BETWEEN '2011-11-01' and '2011-11-30' AND org1.NAME=org.NAME) as number1,( select COUNT(DISTINCT employeeid) from t_hr_emponduty as em2 left join t_hr_org as org2 on org2.id=em2.orgid left join t_hr_employee as ep2 on ep2.id=em2.employeeid where employeeid <>'' and employeeid is not null and em2.positivedate BETWEEN '2011-11-01' and '2011-11-30' and ep2.matriculatesource='402848b62569c53901256d0d8c332110' AND org2.NAME=org.NAME) as number2,( SELECT COUNT(DISTINCT employeeid) from t_hr_emponduty as em3 left join t_hr_org as org3 on org3.id=em3.orgid left join t_hr_employee as ep3 on ep3.id=em3.employeeid where employeeid <>'' and employeeid is not null and em3.positivedate BETWEEN '2011-11-01' and '2011-11-30' and ep3.matriculatesource='402848b62569c53901256d0d8c3321e9' AND org3.NAME=org.NAME) as number3 from t_hr_org as org,t_hr_emponduty as em where org.id=em.orgid group by org.name having count(*)>1
[解决办法]
这样可以了吗?