日期:2014-05-18 浏览次数:20382 次
select [fl], count( case when con=1 then name end) onece , count( case when con>1 then name end) many from ( Select [fl],[name],[phone],COUNT(1) con from tb where datediff(dd,'2011-09-31',date)<0 group by [fl],[name],[phone])t group by [fl] ---免得一份都得不到 [img=http://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/003/monkey/2.gif][/img]
------解决方案--------------------
--> --> (Roy)生成測試數據 declare @T table([id] int,[name] nvarchar(5),[phone] int,[date] Datetime,[fl] nvarchar(3)) Insert @T select 101,N'stone',190982,'2011-08-20',N'lis' union all select 102,N'jhone',180921,'2011-08-21',N'lis' union all select 103,N'bady',188098,'2011-08-22',N'jis' union all select 104,N'liha',190982,'2011-09-10',N'lis' union all select 105,N'blus',190021,'2011-09-10',N'jis' union all select 106,N'clus',123456,'2011-09-10',N'lis' union all select 107,N'clus',123456,'2011-09-10',N'jis' union all select 108,N'clus',123456,'2011-09-11',N'lis' union all select 109,N'clus',123456,'2011-09-13',N'lis' union all select 110,N'blus',190021,'2011-09-14',N'jis' union all select 111,N'poly',198889,'2011-09-14',N'jis' union all select 112,N'stone',190982,'2011-09-20',N'lis' declare @dt varchar(7) set @dt='2011-09' select [fl], count( case when con=1 then name end) onece , count( case when con>1 then name end) many from ( Select [fl],[name],[phone],COUNT(*) con from @T where CONVERT(varchar(7),[date],120)=@dt group by [fl],[name],[phone])t group by [fl] /* fl onece many jis 2 1 lis 2 1 */