请教按人按天统计查询

[Banianer]

[img]http://7d9k2n.com1.z0.glb.clouddn.com/1.JPG[/img]
希望得到以下数据效果，如果当天 YG_No 对应的无数据，则显示为0
[img]http://7d9k2n.com1.z0.glb.clouddn.com/2.JPG[/img]
谢谢！

[许晨旭]

declare @table table
(
	ID INT identity(1,1),
	YG_No varchar(10),
	KD_No varchar(20),
	S_Time datetime
)

insert into @table
select 'G003','3900324532432','20150706' union all
select 'G001','3900324532432','20150703' union all
select 'G002','8802324532432','20150703' union all
select 'G003','3900324532432','20150705' union all
select 'G001','8802324532432','20150705' union all
select 'G001','8802324532432','20150704' union all
select 'G002','8802324532432','20150703' union all
select 'G003','3211032453243','20150706' union all
select 'G002','8802324532432','20150706' union all
select 'G002','6544324532432','20150706' union all
select 'G003','6546324532432','20150706' union all
select 'G003','8802324532432','20150702' union all
select 'G001','3900324532432','20150706' union all
select 'G003','3211324532432','20150706' union all
select 'G003','3900324532432','20150703' union all
select 'G002','8802324532432','20150702' union all
select 'G002','8802324532432','20150703' union all
select 'G003','3900324532432','20150705'


select S_Time,
		COUNT(
		case when left(KD_No,4)='3900' then 1
		end
		) as '3900开头数据',
		COUNT(
		case when left(KD_No,4)='8802' then 1
		end
		) as '8802开头数据',
		COUNT(
		case when left(KD_No,4)!='8802' and left(KD_No,4)!='3900' then 1
		end
		) as '其他开头数据'
from @table
group by S_Time


select S_Time,
		COUNT(
		case when YG_No='G001' then 1
		end
		) as 'G001',
		COUNT(
		case when YG_No='G002' then 1
		end
		) as 'G002',
		COUNT(
		case when YG_No='G003' then 1
		end
		) as 'G003'
from @table
group by S_Time

[卖水果的net]

-- 转换一下，只留下日期，时间不要。
select convert(varchar(10),S_Time,121) as s_time , 
       sum(case when YG_No='G001' then 1 else 0 end ) as 'G001',
       sum(case when YG_No='G002' then 1 else 0 end ) as 'G002',
       sum(case when YG_No='G003' then 1 else 0 end ) as 'G003'
from mytable 
group by convert(varchar(10),S_Time,121)

[Banianer]

感谢两位的回答！