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

2008-11-23 02:53:38 91 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

对SQL基础知识第2版、SQL高级知识第2版、SQL145题第2版-by李岳的书评数学《SQL基础知识》的第一个特点是，系统性地带你走一遍，一条SQL(查询)语句所有涉及的关键字，一个一个的过，全面而细致。这样就不需要你在SQL学习的入门阶段，碰到一个不懂的，就到处去搜，不系统。因为假如这样做，你也不知道，你是否基本搞明白了——关于这个不懂的问题的所有该掌握的知识，以及，它和其他知识的关系是什么。另外要说，第一章中，一条SELECT查询语句的执行顺序。阅读详情

有以下表
姓名语文英语数学
张三 80 80 80
李四 70 70 70
王五 60 60 60

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

最好能够做得通用性好一些...比如有很多人的话应该怎么......

...全文

深夜小酌，50道经典SQL题，真香~ 晚上听说我们村子快解封了，居家办公的日子已不多，久久不能平息~~蹲坑之余，在网上找到了50道所谓经典SQL题，这不就是深夜必备小菜？我用脚叼起拖鞋，从冰箱拿出封印已久的半瓶可乐，打开数日未见的MySQL8，来吧，来场说卷就卷的刷题。现在是6月9号0015分，花了近3小时撸完这小50题，有点困了，不想对所谓标准答案了。。心中有猛虎，何必细嗅蔷薇？我先把自己的结果简单发上来（），相信和我一样爱玩儿的同学们，可以刷一遍后对照一下我的SQL，会找出我SQL中的问题，我再来复盘更新。... 阅读详情

bluefates

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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题~谁能解决一下?

相关推荐