22,210
社区成员
发帖
与我相关
我的任务
分享
select hh.LoginName,ISNULL(gg.cou,0) as '数量' from
(SELECT AA.LoginName,COUNT(DISTINCT AA.LogId)AS cou
FROM d DD,C CC,F FF,e EE,B BB,A AA
WHERE FF.provinceId=EE.ProvinceId AND EE.CityId=BB.CityId AND AA.LoginName=BB.LoginName AND DD.RelationId=CC.RelationId AND AA.LoginName=CC.LoginName
AND AA.LoginDate BETWEEN '2011-08-08 00:00:00' AND '2011-08-09 23:59:59' AND ParentId=1 AND FF.ProvinceId=1
GROUP BY AA.LoginName) as gg right join (select distinct LoginName from b) as hh on gg.LoginName=hh.LoginName
select hh.LoginName,ISNULL(gg.cou,0) as '数量' from
(SELECT AA.LoginName,COUNT(DISTINCT AA.LogId)AS cou
FROM d DD,C CC,F FF,e EE,B BB,A AA
WHERE FF.provinceId=EE.ProvinceId AND EE.CityId=BB.CityId AND AA.LoginName=BB.LoginName AND DD.RelationId=CC.RelationId AND AA.LoginName=CC.LoginName
AND AA.LoginDate BETWEEN '2011-08-08 00:00:00' AND '2011-08-09 23:59:59' AND ParentId=1 AND FF.ProvinceId=1 AND BB.IsCharge=1
GROUP BY AA.LoginName) as gg right join (select distinct LoginName from b) as hh on gg.LoginName=hh.LoginName
--------------------------------------------------------
LoginName 数量
------------- -----------
test 4
tests 2
zhang 0
(3 行受影响)
(1 行受影响)
LoginName 数量
------------- -----------
test 4
tests 0
zhang 0
if exists(select isCharge from a where isCharge = 1)
select
a.LoginName,isnull(count(distinct a.logid),0) as '数量',
from
a,b,c,d,e,f
where
a.LoginName=b.LoginName
and
a.LoginName=c.LoginName
and
c.RelationId=d.RelationId
and
b.CityId=e.CityId
and
e.ProvinceId=f.ProvinceId
and
a.LoginDate between 'xx' and 'xx' ---你要传的参数
and
d.ParentId=xx --你要传的参数
and
f.ProvinceId=xx ---你要传的参数
group by
a.LoginName
else
select
a.LoginName,count(distinct a.logid) as '数量',
from
a,b,c,d,e,f
where
a.LoginName=b.LoginName
and
a.LoginName=c.LoginName
and
c.RelationId=d.RelationId
and
b.CityId=e.CityId
and
e.ProvinceId=f.ProvinceId
and
a.LoginDate between 'xx' and 'xx' ---你要传的参数
and
d.ParentId=xx --你要传的参数
and
f.ProvinceId=xx ---你要传的参数
group by
a.LoginName
having
isnull(count(distinct a.logid),0)<>0