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

[解决办法]

SQL code

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
[解决办法]
  这样可以了吗？

探讨



SQL code
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 <>'' an…… 
 

[解决办法]
三个表（number1，number2，number3）加个Where 条件做匹配