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.o