ms sql复杂统计,该怎么处理

ms sql复杂统计
如动态表有如下数据：
id name(姓名) phone(电话) date fl(分类)
101 stone 190982 2011-08-20 lis
102 jhone 180921 2011-08-21 lis
103 bady 188098 2011-08-22 jis
104 liha 190982 2011-09-10 lis
105 blus 190021 2011-09-10 jis
106 clus 123456 2011-09-10 lis
107 clus 123456 2011-09-10 jis
108 clus 123456 2011-09-11 lis
109 clus 123456 2011-09-13 lis
110 blus 190021 2011-09-14 jis
111 poly 198889 2011-09-14 jis
112 stone 190982 2011-09-20 lis
想要在不生成新表的情况下，统计出表中name列和phone列所对应fl列分组合并后所出现次数为一的有几行，出现次数不为一的有几行：
如要统计2011-09月的数据：
1、分类“lis”所对应name和phone在09月31日前只出现一次的当月记录是“liha 190982” ；即要求显示“1”；
2、分类“lis”所对应name和phone在09月31日前出现多次的记录是“stone 190982 ”出现两次 和“clus 123456 ”出现三次；即要求显示“2”
3、分类“jis”所对应name和phone在09月31日前只出现一次的当月记录是“clus 123456 ”和“poly 198889 ”；即要显示“2”
4、分类“jis”所对应name和phone在09月31日前出现多次的记录是“blus 190021 ”；即要求显示“1”

须显示结果如下，fl为分类，onece为出现一次，many为出现多次
fl onece many
lis 1 2
jis 2 1

[解决办法]

SQL code

select     [fl],    count( case when con=1 then name end) onece ,    count( case when con>1 then name end) manyfrom (Select [fl],[name],[phone],COUNT(1) confrom tb where datediff(dd,'2011-09-31',date)<0group by [fl],[name],[phone])tgroup by [fl]---免得一份都得不到 [img=http://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/003/monkey/2.gif][/img]
[解决办法]
SQL code
--> --> (Roy)生成測試數據 declare @T table([id] int,[name] nvarchar(5),[phone] int,[date] Datetime,[fl] nvarchar(3))Insert @Tselect 101,N'stone',190982,'2011-08-20',N'lis' union allselect 102,N'jhone',180921,'2011-08-21',N'lis' union allselect 103,N'bady',188098,'2011-08-22',N'jis' union allselect 104,N'liha',190982,'2011-09-10',N'lis' union allselect 105,N'blus',190021,'2011-09-10',N'jis' union allselect 106,N'clus',123456,'2011-09-10',N'lis' union allselect 107,N'clus',123456,'2011-09-10',N'jis' union allselect 108,N'clus',123456,'2011-09-11',N'lis' union allselect 109,N'clus',123456,'2011-09-13',N'lis' union allselect 110,N'blus',190021,'2011-09-14',N'jis' union allselect 111,N'poly',198889,'2011-09-14',N'jis' union allselect 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) manyfrom (Select [fl],[name],[phone],COUNT(*) confrom @T where CONVERT(varchar(7),[date],120)=@dtgroup by [fl],[name],[phone])tgroup by [fl]/*fl    onece    manyjis    2    1lis    2    1*/