SQL语句

 

 ①、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

转载于:https://www.cnblogs.com/hongyan5682/p/3630965.html