再求:变表的列为行

辉歌 2006-01-09 11:10:57
我曾在该版请教过:
http://community.csdn.net/Expert/topic/4484/4484863.xml?temp=.6107904
zjcxc(邹建) 大哥迅速的解决了问题。

现在情况有所变化。

有一个表,结构如下:
城市名 状态 其他字段(不重要)
杭州 0 ...
杭州 1 ...
杭州 -1 ...
杭州 1 ...
武汉 1 ...
武汉 1 ...
北京 -1 ...
北京 -1 ...

其中城市名字段的值不固定。

现在要求统计这个表:
1,统计武汉,北京,上海,杭州 ,西安,深圳等等14个已知具体城市名的出现的次数。同时将其他城市名成出现的次数汇总到“其他”字段里。
2,对每个城市名,要求统计三种状态下出现的次数。 状态=1 和状态!=1,和所有状态
3,对每个城市出现的次数做一个比例计算: 状态1出现的次数/ 所有状态出现的次数

最终,能出现如下一个表:
情况 杭州 武汉 北京 南京 西安深圳,....,其他
通过 111 222 333 555 122 223.... 456
未通过 111 222 333 233 111 89 .... 356
总量 222 444 666 788 234 266... 899
比例 50% 50% 50% 23% 57% 90% ... %65

但是不知道该如何下手。请高手指教。

如果对比例计算和未通过数据统计很麻烦,可以不做。

...全文
180 14 打赏 收藏 举报
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
caizhen2000_82 2006-01-10
真是牛人一大堆啊!!
  • 打赏
  • 举报
回复
mylover002 2006-01-10
高手,
  • 打赏
  • 举报
回复
辉歌 2006-01-09
谢谢楼上的各位大哥。
如果将已知的14个城市名放在表里,该怎么做呢?

表 units
name
北京
上海
西安
深圳
...

另外通过和未通过的状态,
通过的状态是 status =1
未通过的状态是 status=-1,0


  • 打赏
  • 举报
回复
辉歌 2006-01-09
SQL SERVER版就是牛人多。
  • 打赏
  • 举报
回复
zzit0721 2006-01-09
create table t(city varchar(20),status int)----测试数据
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',-1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1
select distinct city
from t
select *
from t

declare
@sql varchar(1000)
set @sql=''
select @sql=@sql+','+city+'=isnull(sum(case when city='''+city+''' and status=1 then 1
when city='''+city+''' and status=-1 then 1 end ),0)'
from t
group by city


set @sql='select 情况=isnull(case status when 1 then ''通过'' when -1 then ''未通过'' end,''总量'')'+@sql+'from t group by
status with rollup'
exec (@sql)
这是动态的SQL,等我吃饭回来给你考虑下比例哈,呵呵
  • 打赏
  • 举报
回复
wang520d 2006-01-09

libin_ftsafe(子陌红尘) 一看就知道是牛人
  • 打赏
  • 举报
回复
子陌红尘 2006-01-09
create table t(city varchar(20),status int)
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1


select a.*
from
(select
情况=isnull(case status when 1 then '通过' when -1 then '未通过' end,'总量'),
武汉=rtrim(sum(case city when '武汉' then 1 else 0 end)),
北京=rtrim(sum(case city when '北京' then 1 else 0 end)),
上海=rtrim(sum(case city when '上海' then 1 else 0 end)),
杭州=rtrim(sum(case city when '杭州' then 1 else 0 end)),
西安=rtrim(sum(case city when '西安' then 1 else 0 end)),
深圳=rtrim(sum(case city when '深圳' then 1 else 0 end)),
其他=rtrim(sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end))
from
t
group by
status with rollup
union
select
情况='比例',
武汉=rtrim(cast(sum(case when city='武汉' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '武汉' then 1 else 0 end)=0 then 1 else sum(case city when '武汉' then 1 else 0 end) end) as int))+'%',
北京=rtrim(cast(sum(case when city='北京' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '北京' then 1 else 0 end)=0 then 1 else sum(case city when '北京' then 1 else 0 end) end) as int))+'%',
上海=rtrim(cast(sum(case when city='上海' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '上海' then 1 else 0 end)=0 then 1 else sum(case city when '上海' then 1 else 0 end) end) as int))+'%',
杭州=rtrim(cast(sum(case when city='杭州' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '杭州' then 1 else 0 end)=0 then 1 else sum(case city when '杭州' then 1 else 0 end) end) as int))+'%',
西安=rtrim(cast(sum(case when city='西安' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '西安' then 1 else 0 end)=0 then 1 else sum(case city when '西安' then 1 else 0 end) end) as int))+'%',
深圳=rtrim(cast(sum(case when city='深圳' and status=1 then 1 else 0 end)*100.0/
(case when sum(case city when '深圳' then 1 else 0 end)=0 then 1 else sum(case city when '深圳' then 1 else 0 end) end) as int))+'%',
其他=rtrim(cast(sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') and status=1 then 1 else 0 end)*100.0/
(case when sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end)=0 then 1 else sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end) end) as int))+'%'
from
t) a
order by
(case 情况 when '未通过' then 1 when '通过' then 2 when '总量' then 3 esle 4 end)

/*
情况 武汉 北京 上海 杭州 西安 深圳 其他
------ ------ ------ ------ ------ ------ ------ ------
通过 2 0 0 3 0 0 0
未通过 0 2 0 1 0 0 0
总量 2 2 0 4 0 0 0
比例 100% 0% 0% 75% 0% 0% 0%
*/

drop table t
  • 打赏
  • 举报
回复
lw1a2 2006-01-09
create table #t(city varchar(20),s int)

insert into #t values('杭州',0)
insert into #t values('杭州',1)
insert into #t values('杭州',-1)
insert into #t values('杭州',1)
insert into #t values('武汉',1)
insert into #t values('武汉',1)
insert into #t values('北京',-1)
insert into #t values('北京',-1)


select '通过',
sum(case city when '杭州' then 1 else 0 end) as '杭州',
sum(case city when '武汉' then 1 else 0 end) as '武汉',
sum(case city when '北京' then 1 else 0 end) as '北京'
from #t
where s=1
union all
select '未通过',
sum(case city when '杭州' then 1 else 0 end) as '杭州',
sum(case city when '武汉' then 1 else 0 end) as '武汉',
sum(case city when '北京' then 1 else 0 end) as '北京'
from #t
where s<>1
union all
select '总量',
sum(case city when '杭州' then 1 else 0 end) as '杭州',
sum(case city when '武汉' then 1 else 0 end) as '武汉',
sum(case city when '北京' then 1 else 0 end) as '北京'
from #t
union all
select '比例',
isnull((select sum(1.) from #t where city='杭州' and s=1)/(select sum(1.) from #t where city='杭州'),0),
isnull((select sum(1.) from #t where city='武汉' and s=1)/(select sum(1.) from #t where city='武汉'),0),
isnull((select sum(1.) from #t where city='北京' and s=1)/(select sum(1.) from #t where city='北京'),0)
from #t
group by city

--以上只是3个城市的
  • 打赏
  • 举报
回复
子陌红尘 2006-01-09
提供一个不计算比例的:
----------------------------------------------------------------------------------------------
create table t(city varchar(20),status int)
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1

select
情况=isnull(case status when 1 then '通过' when -1 then '未通过' end,'总量'),
武汉=rtrim(sum(case city when '武汉' then 1 else 0 end)),
北京=rtrim(sum(case city when '北京' then 1 else 0 end)),
上海=rtrim(sum(case city when '上海' then 1 else 0 end)),
杭州=rtrim(sum(case city when '杭州' then 1 else 0 end)),
西安=rtrim(sum(case city when '西安' then 1 else 0 end)),
深圳=rtrim(sum(case city when '深圳' then 1 else 0 end)),
其他=rtrim(sum(case when city not in('武汉','北京','上海','杭州','西安','深圳') then 1 else 0 end))
from
t
group by
status with rollup

/*
情况 武汉 北京 上海 杭州 西安 深圳 其他
------ ------ ------ ------ ------ ------ ------ ------
未通过 0 2 0 1 0 0 0
通过 2 0 0 3 0 0 0
总量 2 2 0 4 0 0 0
*/

drop table t
  • 打赏
  • 举报
回复
hbj88 2006-01-09
说明不太清楚!!!!!
  • 打赏
  • 举报
回复
zzit0721 2006-01-09
呵呵,LZ自己定制下比例的格式就完了
  • 打赏
  • 举报
回复
zzit0721 2006-01-09
create table t(city varchar(20),status int)----测试数据
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',-1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1
declare
@sql varchar(1000)--@sql求数量判断值
set @sql=''
select @sql=@sql+','+city+'=isnull(sum(case when city='''+city+''' and status=1 then 1
when city='''+city+''' and status=-1 then 1 end ),0)'
from t
group by city


set @sql='select 情况=isnull(case status when 1 then ''通过'' when -1 then ''未通过'' end,''总量'')'+@sql+'from t group by
status with rollup'


declare
@sq varchar(1000)---@sq求比例
set @sq=''
select @sq=@sq+','+city+'=isnull(convert(float,sum(case when city='''+city+''' and status=-1 then 1 end))/convert(float,sum(case when city='''+city+''' and status=1 then 1 end)),0)'
from t
group by city
set @sq='select 情况=''比例'' '+@sq+' from t'
set @sq=@sql+' union all '+@sq
exec (@sq)
呵呵,午休了才来了,不好意思,晚了哈,楼主
  • 打赏
  • 举报
回复
子陌红尘 2006-01-09
create table u(name varchar(20))
insert into u select '北京'
insert into u select '上海'
insert into u select '西安'
insert into u select '深圳'

create table t(city varchar(20),status int)
insert into t select '杭州',1
insert into t select '杭州',1
insert into t select '杭州',-1
insert into t select '杭州',1
insert into t select '武汉',1
insert into t select '武汉',1
insert into t select '北京',-1
insert into t select '北京',-1


declare @s varchar(8000),@v varchar(1000)
set @s='select 情况=isnull(status,''总量'')'
set @v=''
select @s=@s+','+name+'=rtrim(sum(case city when '''+name+''' then 1 else 0 end))',@v=@v+','+''''+name+'''' from u

set @s=@s+',其他=rtrim(sum(case when city not in('+stuff(@v,1,1,'')+') then 1 else 0 end))
from
(select status=(case status when 1 then ''未通过'' else ''通过'' end),city from t) a
group by
status with rollup'

exec(@s)

/*
情况 北京 上海 西安 深圳 其他
------ ------ ------ ------ ------ ------
未通过 2 0 0 0 1
通过 0 0 0 0 5
总量 2 0 0 0 6
*/

drop table t,u
  • 打赏
  • 举报
回复
zhaoanle 2006-01-09
--建测试表
create table 表A(城市名 varchar(10),状态 float(1,1))
insert 表A values('杭州', 0)
insert 表A values('杭州', 1)
insert 表A values('杭州', -1)
insert 表A values('杭州', 1)
insert 表A values('武汉', 1)
insert 表A values('武汉', 1)
insert 表A values('北京', -1)
insert 表A values('北京', -1)
go
--查询语句
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
declare @sql4 varchar(8000)

set @sql1='select ''1通过'' as ''情况'','
select @sql1=@sql1+'sum(case when 状态=1 and 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A) a
set @sql1=left(@sql1,len(@sql1)-1)+' from 表A'

set @sql2='select ''2未通过'','
select @sql2=@sql2+'sum(case when 状态!=1 and 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A) a
set @sql2=left(@sql2,len(@sql2)-1)+' from 表A'

set @sql3='select ''3总量'','
select @sql3=@sql3+'sum(case when 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A ) a
set @sql3=left(@sql3,len(@sql3)-1)+' from 表A '

set @sql4='select ''4通过比例(%)'','
select @sql4=@sql4+'sum(case when 状态=1 and 城市名='''+a.城市名+'''then 1 else 0 end)*100/sum(case when 城市名='''+a.城市名+'''then 1 else 0 end) as '''+a.城市名+''','
from (select distinct 城市名 from 表A) a
set @sql4=left(@sql4,len(@sql4)-1)+' from 表A '

exec (@sql1+' union '+@sql2+' union '+@sql3+' union '+@sql4 +'order by 情况 ')
/*--结果
情况 北京 杭州 武汉
------------ ----------- ----------- -----------
1通过 0 2 2
2未通过 2 2 0
3总量 2 4 2
4通过比例(%) 0 50 100

这样应该可以了吧?*/
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server

3.4w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2006-01-09 11:10
社区公告
暂无公告