有一道比较难的sql题~谁能解决一下?

bluefates 2008-11-23 02:53:38
有以下表
姓名 语文 英语 数学
张三 80 80 80
李四 70 70 70
王五 60 60 60

用SQL语句查询
张三 李四 王五
语文 80 70 60
英语 80 70 60
数学 80 70 60


最好能够做得通用性好一些...比如有很多人的话应该怎么......
...全文
64 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2008-11-23
  • 打赏
  • 举报
 回复 
上午好象帮你写过.



create proc p_zj

       @tbname sysname, --要处理的表名

       @fdname sysname, --做为转换的列名

       @new_fdname sysname='' --为转换后的列指定列名

as

declare @s1 varchar(8000) , @s2 varchar(8000),

        @s3 varchar(8000) , @s4 varchar(8000),

        @s5 varchar(8000) , @i varchar(10)

select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'

select @s1 = @s1 + ',@' + @i + ' varchar(8000)',

       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''

       else @new_fdname + '=' end + '''''' + name + '''''''',

       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 

       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',

       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,

       @s5 = @s5 + '+'' union all ''+@' + @i,

       @i=cast(@i as int)+1

from syscolumns

where object_id(@tbname)=id and name<>@fdname



select @s1=substring(@s1,2,8000),

       @s2=substring(@s2,2,8000),

       @s4=substring(@s4,2,8000),

       @s5=substring(@s5,16,8000)

exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '

exec(' + @s5 + ')')

go



create table tb(姓名 varchar(10),语文 int,英语 int,数学 int)

insert into tb values('张三' ,80 ,80 ,80 )

insert into tb values('李四' ,70 ,70 ,70 )

insert into tb values('王五' ,60 ,60 ,60 )

go



exec p_zj 'tb', '姓名' , '姓名'



drop table tb

drop proc p_zj



/*

姓名   张三          李四          王五          

---- ----------- ----------- ----------- 

数学   80          70          60

英语   80          70          60

语文   80          70          60



（所影响的行数为 3 行）

*/
bbb332 2008-11-23
  • 打赏
  • 举报
 回复
--行列互转
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂

整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、行互列
--> --> (Roy)生成測試數據

if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')


生成静态:

select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]

GO
动态:

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78

(2 行受影响)
*/

------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)

--2000方法:
动态:

declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))

生成动态:

select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]

go

--2005方法:

动态:

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')

生成静态:

select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:

/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337

(2 行受影响)
*/

go

--2、列转行
--> --> (Roy)生成測試數據

if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go

--2000:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序

生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]

go
--2005:

动态:

declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')

go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b

生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78

(8 行受影响)
*/
水族杰纶 2008-11-23
  • 打赏
  • 举报
 回复 
if object_id('ta')is not null drop table ta

go

create table ta(姓名 varchar(5), 语文 int, 英语 int, 数学 int) 

insert ta select '张三', 80, 80, 80 

insert ta select '李四', 70, 70, 70 

insert ta select '王五', 60, 60, 60 

if object_id('tempdb..#')is not null drop table #

go

select id=identity(int,1,1),姓名 into # from ta

declare @s varchar(8000)

declare @s1 varchar(8000)

declare @s2 varchar(8000)

declare @s3 varchar(8000)

select @s = isnull(@s+',','') + '[col'+ltrim(id)+'] = max(case when 姓名 = '''+ltrim(姓名) +''' then 姓名 else null end)'

from #

set @s = 'select ''姓名'' as col0 ,'+ @s + ' from ta  union all ' 

select @s1 = isnull(@s1+',','') + '[col'+ltrim(id)+'] = max(case when 姓名 = '''+ltrim(姓名) +''' then ltrim(语文) else null end)'

from #

set @s1 = 'select ''语文'' as col0 ,'+ @s1 + ' from ta   union all ' 

select @s2 = isnull(@s2+',','') + '[col'+ltrim(id)+'] = max(case when 姓名 ='''+ltrim(姓名) +''' then ltrim(英语) else null end)'

from #

set @s2 = 'select ''英语'' as col0 ,'+ @s2 + ' from ta   union all ' 

select @s3 = isnull(@s3+',','') + '[col'+ltrim(id)+'] = max(case when 姓名 = '''+ltrim(姓名) +''' then ltrim(数学) else null end) '

from #

set @s3 = 'select ''数学'' as col0 ,'+ @s3 + ' from ta ' 

exec(@s+@s1+@s2+@s3)

/*col0 col1         col2         col3         

---- ------------ ------------ ------------ 

姓名   张三           李四           王五

语文   80           70           60

英语   80           70           60

数学   80           70           60*/
bbb332 2008-11-23
  • 打赏
  • 举报
 回复
行列到换！
精华贴里有！！
让你望见影子的墙 2008-11-23
  • 打赏
  • 举报
 回复
http://topic.csdn.net/u/20080505/10/95d1a27d-345d-42b1-a362-1d2156c8d6f5.html
liangCK 2008-11-23
  • 打赏
  • 举报
 回复 
create proc p_zj

       @tbname sysname, --要处理的表名

       @fdname sysname, --做为转换的列名

       @new_fdname sysname='' --为转换后的列指定列名

as

declare @s1 varchar(8000) , @s2 varchar(8000),

        @s3 varchar(8000) , @s4 varchar(8000),

        @s5 varchar(8000) , @i varchar(10)

select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'

select @s1 = @s1 + ',@' + @i + ' varchar(8000)',

       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''

       else @new_fdname + '=' end + '''''' + name + '''''''',

       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 

       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',

       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,

       @s5 = @s5 + '+'' union all ''+@' + @i,

       @i=cast(@i as int)+1

from syscolumns

where object_id(@tbname)=id and name<>@fdname



select @s1=substring(@s1,2,8000),

       @s2=substring(@s2,2,8000),

       @s4=substring(@s4,2,8000),

       @s5=substring(@s5,16,8000)

exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '

exec(' + @s5 + ')')

go



create table tb(姓名 varchar(10),语文 int,英语 int,数学 int)

insert into tb values('张三' ,80 ,80 ,80 )

insert into tb values('李四' ,70 ,70 ,70 )

insert into tb values('王五' ,60 ,60 ,60 )

go



exec p_zj 'tb', '姓名' , '姓名'



drop table tb

drop proc p_zj



/*

姓名   张三          李四          王五          

---- ----------- ----------- ----------- 

数学   80          70          60

英语   80          70          60

语文   80          70          60



（所影响的行数为 3 行）

*/
一道携程SQL笔试题
来自一位朋友最近做的携程笔试题，其中有一道关于SQL的题目是这样的： 一、题目 Mysql数据库中有如下两张表，app表为应用表，储存的是应用相关信息；alert表为预警表，储存的是各应用在一段时间内的预警信息。 app：...
一道有趣的sql题，统计复旦用户8月练题情况
这是一道非常有趣的sql题，第一部分是题的描述，第二部分是题解决。不妨看完题目先思考一下，或者实际运行一下。也可直接从题解看起，题解并非直述答案，而是从每一个sql知识点由浅入深，深入浅出。在牛客上这是...
预编译真的能完美防御SQL注入吗？
在之前面试的时候，我还是太菜了，有一道面试题还是值得研究研究的。面试官问我，怎样完全避免sql注入？我想起了刚毕业时校招有一次也被问过一样的问题，当时我说的是：“基本上预编译就能解决了。”当时并没有对我...
每日一道SQL题（第N高的薪水）
拿到一道sql题，首先感觉一下，可能会出现的问题，比如这道题，薪水问题，薪水会不会出现同名同薪的问题？？要怎么考虑？ 又比如mysql怎么写？如果是orcale呢？oracle有窗口函数，那么mysql呢？ 排名是数据库中的一...
SQL经典50题练习
SQL这套50题在网上流传很广，是比较经典、全面的一套题，最近刚入职数据开发工程师，SQL需要加强，因此自然不能错过这套题，答案都是自己写的，有错误或者可以改进的地方欢迎指出。 数据表介绍 题中所需数据来自...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章