单条语句查询
(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 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
传的图能不能看到查询的正确结果可是我拼了三条语句后成了这样就是不论有多少个name,number1的值永远是一样,求大侠指明
查询的正确结果可是我拼了三条语句后成了这样就是不论有多少个name,number1的值永远是一样,求大侠指明
正确的应为:
name number1 number2 number3
xxx 6 0 0
aaaa 0 0 0
cccc 0 0 0
dddd 0 0 0
结果还是这样:
name number1 number2 number3
xxx 6 0 0
aaaa 6 0 0
cccc 6 0 0
dddd 6 0 0xxx对应的为第一个语句number1的值为6,现在问题是后面所有的name跟着全部为6。group by 哪里有问题