• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求一SQL语句

Hegemon 2008-04-04 02:53:08
表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
...全文
64 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
-狙击手- 2008-04-04
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 行)
*/
回复
JJkukow 2008-04-04
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
回复
Limpire 2008-04-04
--> 测试数据: #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%
*/
回复
Limpire 2008-04-04
--> 测试数据: #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
*/
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-04 02:53
社区公告
暂无公告