求一SQL语句

[Hegemon]

表A
F_code F_name F_Flag
0001 aaa 1
0001 aaa 2
0002 bbb 1
0003 ccc 1
0003 bbb 2

统计结果如下：
总数(F_Flag=1数量) 数量1(F_Flag=2) 数量2(说明见下) 百分率(数量2/总数)
3 2 1 33.33%


数量2：同一编号有F_Flag=1,但是没有F_Flag=2的数量
如：0001有1，2两状态，0002就只有1没有2，按上表就是数量2=1

[Limpire]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (F_code varchar(4),F_name varchar(3),F_Flag int)
insert into #T
select '0001','aaa',1 union all
select '0001','aaa',2 union all
select '0002','bbb',1 union all
select '0003','ccc',1 union all
select '0003','bbb',2

select
	总数=sum(case F_Flag when 1 then 1 else 0 end),
	数量1=sum(case F_Flag when 2 then 1 else 0 end),
	数量2=(select count(distinct F_code) from #T a where not exists (select 1 from #T where F_code=a.F_code and F_Flag=2)),
	百分率=1.0*(select count(distinct F_code) from #T a where not exists (select 1 from #T where F_code=a.F_code and F_Flag=2))/sum(case F_Flag when 1 then 1 else 0 end)
from #T

/*
总数        数量1       数量2       百分率
----------- ----------- ----------- --------------
3           2           1           0.333333333333
*/

[Limpire]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (F_code varchar(4),F_name varchar(3),F_Flag int)
insert into #T
select '0001','aaa',1 union all
select '0001','aaa',2 union all
select '0002','bbb',1 union all
select '0003','ccc',1 union all
select '0003','bbb',2

select
	总数=sum(case F_Flag when 1 then 1 else 0 end),
	数量1=sum(case F_Flag when 2 then 1 else 0 end),
	数量2=(select count(distinct F_code) from #T a where not exists (select 1 from #T where F_code=a.F_code and F_Flag=2)),
	百分率=ltrim(cast(100.0*(select count(distinct F_code) from #T a where not exists (select 1 from #T where F_code=a.F_code and F_Flag=2))/sum(case F_Flag when 1 then 1 else 0 end) as numeric(10,2)))+'%'
from #T
/*
总数        数量1       数量2       百分率
----------- ----------- ----------- --------------
3           2           1           33.33%
*/

[JJkukow]

select a.[总数],a.[数量1],a.[数量2],[百分率]=cast(a.[数量1]/a.[总数])
from
(select [总数]=sum(f_flag),0,0 from 表A group by f_code having f_flag=1
union all select 0,[数量1]=sum(f_flag),0 from 表A group by f_code having f_flag=1
union all select 0,0,[数量2]=sum(f_flag) from 表A group by f_code having f_flag=2)a

[-狙击手-]

declare @t table(F_code varchar(4), F_name varchar(4),F_Flag int)
insert @t select
   '0001','aaa', 1 union select
   '0001','aaa', 2 union select
   '0002','bbb', 1 union select
   '0003','ccc', 1 union select
   '0003','bbb', 2  




select *,[百分率(数量2/总数)]=cast([数量2(说明见下)]*100.0/[总数(F_Flag=1数量)] as numeric(12,2))
from(
select [总数(F_Flag=1数量)]=(select count(1) from @t where f_flag = 1),
       [数量1(F_Flag=2)] =(select count(1) from @t where f_flag = 2),
       [数量2(说明见下)]=(select count(distinct f_code)
						from @t a
						where f_flag = 1
						and not exists(select 1 from @t where f_code = a.f_code and f_flag = 2))) a

/*
总数(F_Flag=1数量) 数量1(F_Flag=2) 数量2(说明见下)   百分率(数量2/总数)    
-------------- ------------- ----------- -------------- 
3              2             1           33.33

（所影响的行数为 1 行）
*/