①、select v.ZoneName,v.EmpName as
渠道经理,v.AreaName,count(1) as 销量,count(distinct ChannelNo) as 参与网点数,(select
count(1)from(select ChannelNo,C=count(1) from V_IMEISale v1 where ZoneName=‘潮安‘
and AreaName=‘潮安南片‘and EmpName=v.EmpName and RegisterTime between ‘2014-03-01‘
and ‘2014-03-28‘ group by ChannelNo having count(1)>10)as temp)as
销量大于10
from V_IMEISale v
where v.ZoneName=‘潮安‘ and
v.AreaName=‘潮安南片‘ and v.RegisterTime between ‘2014-03-01‘ and
‘2014-03-28‘
group by v.ZoneName,v.AreaName,EmpName
②、SELECT ZoneName,AreaName,EmpName,COUNT(1) AS
‘Total‘,
(SELECT COUNT(1) FROM (SELECT xzy = 1 FROM dbo.V_IMEISale
WHERE ZoneName = V.ZoneName AND AreaName = V.AreaName AND EmpName =
V.EmpName And RegisterTime >= ‘2014/3/1 0:00:00‘ And RegisterTime <=
‘2014/3/31
23:59:59‘
GROUP BY ChannelNo) A) AS
‘NetShops‘,
(SELECT COUNT(1) FROM (SELECT ChannelNo,C=COUNT(1) FROM dbo.V_IMEISale
WHERE ZoneName = V.ZoneName AND AreaName = V.AreaName AND EmpName =
V.EmpName And RegisterTime >= ‘2014/3/1 0:00:00‘ And RegisterTime <=
‘2014/3/31
23:59:59‘
GROUP BY ChannelNo HAVING COUNT(1) > 10)B) AS
‘Specified‘
FROM V_IMEISale V Where 1 = 1 And RegisterTime >= ‘2014/3/1 0:00:00‘
And RegisterTime <= ‘2014/3/31 23:59:59‘ GROUP BY
ZoneName,AreaName,EmpName,EmpID
③、select
v.ZoneName,v.AreaName,v.EmpName,v.ChannelNo,v.ShopFrontName,(select
count(1)from(select ChannelNo from V_IMEISale v1 where ZoneName=‘潮安‘ and
AreaName=‘潮安南片‘and EmpName=v.EmpName and ChannelNo=v.ChannelNo and RegisterTime
between ‘2014-02-28‘ and ‘2014-03-28‘)as temp)as 销量
from V_IMEISale
v
where v.ZoneName=‘潮安‘ and v.AreaName=‘潮安南片‘ and v.RegisterTime between
‘2014-02-28‘ and ‘2014-03-28‘
group by
v.ZoneName,v.AreaName,EmpName,ChannelNo,ShopFrontName