导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一句sql语句

kylin81 2007-12-11 08:20:34
我有一个表,结构如下
gh rq1 rq2 rq3 rq4 rq5
001 A C D B C
002 D A C A B
003 C B D A D


现求一句SQL语句实现如下结果集
gh A B C D
001 1 1 2 1
002 2 1 1 1
003 1 1 1 2

这个语句怎么请啊?
...全文
84 点赞 收藏 8
写回复
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
wuxinyuzhu 2007-12-11

--借用鸟儿的数据用一下
create table a(gh varchar(20),rq1 varchar(20),rq2 varchar(20),rq3 varchar(20),rq4 varchar(20),rq5 varchar(20))
insert a select '001','A','C','D','B','C'
union all select '002','D','A', 'C','A', 'B'
union all select '003','C','B', 'D','A', 'D'



declare @s nvarchar(4000)
set @s=''
select @s=isnull(@s+' union all ','')+' select gh,'+quotename(name)+ ' as name from a'
from syscolumns where id=object_id('a') and name<>'gh'

set @s=stuff(@s,1,12,'')
exec('select gh,sum(case when name =''a'' then 1 else 0 end ) a,
sum(case when name =''b'' then 1 else 0 end ) b,
sum(case when name =''c'' then 1 else 0 end ) c,
sum(case when name =''d'' then 1 else 0 end ) d from ('+@s+') a group by gh')

回复
晓风残月0110 2007-12-11
看见发哥了,总是看不到回的帖
回复
晓风残月0110 2007-12-11
应该有人啊,刷
回复
fa_ge 2007-12-11
鸟儿是用动态写的
回复
fa_ge 2007-12-11

create table # (gh varchar(3),rq1 varchar(2),rq2 varchar(2),rq3 varchar(2),rq4 varchar(2),rq5 varchar(2))
insert into # select '001','a','c','d','b','c'
insert into # select '002','d','a','c','a','b'
insert into # select '003','c','b','d','a','d'


select
gh,sum(case when rq1='a' then 1 else 0 end)as 'a',
sum(case when rq1='b' then 1 else 0 end)as 'b',
sum(case when rq1='c' then 1 else 0 end)as 'c',
sum(case when rq1='d' then 1 else 0 end)as 'd'
from
(
select gh,rq1 from #
union all
select gh,rq2 from #
union all
select gh,rq3 from #
union all
select gh,rq4 from #
union all
select gh,rq5 from #
)a
group by gh

/*
gh a b c d
---- ----------- ----------- ----------- -----------
001 1 1 2 1
002 2 1 1 1
003 1 1 1 2

(所影响的行数为 3 行)
*/
回复
chuifengde 2007-12-11
create table a(gh  varchar(20),rq1 varchar(20),rq2 varchar(20),rq3 varchar(20),rq4 varchar(20),rq5  varchar(20)) 
insert a select '001','A','C','D','B','C'
union all select '002','D','A', 'C','A', 'B'
union all select '003','C','B', 'D','A', 'D'

declare @sql varchar(8000)
declare @a table(x varchar(20))

select @sql='select gh'
insert @a
select rq1 from a
union all select rq2 from a union all select rq3 from a union all select rq4 from a union all select rq5 from a
select @sql=@sql+',sum(case when rq1='''+x+''' then 1 else 0 end +
case when rq2='''+x+''' then 1 else 0 end +
case when rq3='''+x+''' then 1 else 0 end +
case when rq4='''+x+''' then 1 else 0 end +
case when rq5='''+x+''' then 1 else 0 end
) ['+x+']' from (select distinct * from @a)aa

select @sql=@sql+' from a group by gh'
exec(@sql)
--result
/*gh A B C D
-------------------- ----------- ----------- ----------- -----------
001 1 1 2 1
002 2 1 1 1
003 1 1 1 2
*/
回复
dawugui 2007-12-11
怎么个算法?
回复
wzy_love_sly 2007-12-11
create table # (gh varchar(3),rq1 varchar(2),rq2 varchar(2),rq3 varchar(2),rq4 varchar(2),rq5 varchar(2))
insert into # select '001','a','c','d','b','c'
insert into # select '002','d','a','c','a','b'
insert into # select '003','c','b','d','a','d'


select gh,
sum(case when rq1='a' then 1 else 0 end+case when rq2='a' then 1 else 0 end+case when rq3='a' then 1 else 0 end + case when rq4='a' then 1 else 0 end+ case when rq5='a' then 1 else 0 end) as 'a',
sum(case when rq1='b' then 1 else 0 end+case when rq2='b' then 1 else 0 end+case when rq3='b' then 1 else 0 end + case when rq4='b' then 1 else 0 end+ case when rq5='b' then 1 else 0 end) as 'b',
sum(case when rq1='c' then 1 else 0 end+case when rq2='c' then 1 else 0 end+case when rq3='c' then 1 else 0 end + case when rq4='c' then 1 else 0 end+ case when rq5='c' then 1 else 0 end) as 'c',
sum(case when rq1='d' then 1 else 0 end+case when rq2='d' then 1 else 0 end+case when rq3='d' then 1 else 0 end + case when rq4='d' then 1 else 0 end+ case when rq5='d' then 1 else 0 end) as 'd'
from # group by gh


001 1 1 2 1
002 2 1 1 1
003 1 1 1 2
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告