面试的时候遇到的数据库SQL问题,没写过,然后研究了一下,现将结果记录下来,方便以后查询。 题目1:将表tbltest1的行列互换 表结构: student kemu fenshu student1 语文 80 student1 数学 90 student1 英语 85 student2 语文 85 student2 数学 92 student
面试的时候遇到的数据库SQL问题,没写过,然后研究了一下,现将结果记录下来,方便以后查询。
题目1:将表tbltest1的行列互换
表结构:
student kemu fenshu
student1 语文 80
student1 数学 90
student1 英语 85
student2 语文 85
student2 数学 92
student2 英语 82
变成:
student 语文 数学 英语
student1 80 90 85
student2 85 92 82
SQLserver的sql语句:
declare @sql varchar(4000)
set @sql = 'select student'
select @sql = @sql + ',sum(case kemu when '''+ kemu +''' then fenshu else 0 end)['+ kemu+']'
from (select distinct kemu from tbltest1) as a
set @sql = @sql + ' from tbltest1 group by student'
exec(@sql)
或者
select student,sum(case kemu when '语文' then fenshu else 0 end) 语文,sum(case kemu when '数学' then fenshu else 0 end) 数学,sum(case kemu when '英语' then fenshu else 0 end) 英语 from tbltest group by student
注:个人觉得上面的好。如果一两个选项可以使用下面的sql,如果选项多上面的sql就显的方便的多。
2005的话好像还有个函数可以用,等研究好了再发上来。
题目2:合并
表结构tbltest2:
id strings
1 my
1 name
1 is
1 xudayu
2 hello
2 world
转化成:
id strings
1 my name is xudayu
2 hello world
SQLServer的sql语句:
--创建一个合并的函数
create function fliehebin(@id int)
returns varchar(5000)
as
begin
declare @str varchar(5000)
set @str=''
select @str=@str + cast(strings as varchar(50)) +' ' from tbltest2 where id=@id
set @str=subString(@str,1,len(@str))
return(@str)
end
go
--调用自定义函数得到结果
select distinct id,dbo.fliehebin(id) from tbltest2
=====================================================================
传说通用的, 如下:
IF EXISTS( select * from sysobjects where xtype='U'and name ='data2' )
Begin
Drop table data2
End
CREATE TABLE [data2] (
[人员编号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[基本工资] [numeric](18, 2) NULL ,
[奖金] [numeric](18, 2) NULL ,
[合计] [numeric](19, 2) NULL ,
CONSTRAINT [PK_data2] PRIMARY KEY CLUSTERED
(
[人员编号]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert data2 select 'a1',1.00,11.00,111.00
insert data2 select 'a2',2.00,22.00,222.00
insert data2 select 'a3',3.00,33.00,333.00
insert data2 select 'a100',100.00,100.00,100.00
go
drop PROCEDURE AVB_IniTable
go
/*
author:nyb
time :2005/04/22
fixtime :
aim :转置行和列
input :@TableNane
执行:
EXECUTE AVB_IniTable 'data2'
*/
Create PROCEDURE AVB_IniTable
@TableNane varchar(128)
AS
DECLARE @string VARCHAR(8000)
--1 创建View
IF EXISTS( select * from sysobjects where xtype='V'and name ='V_Temp' )
Begin
Drop view V_Temp
End
SELECT @string = ' Create view V_Temp as select * from ' + @TableNane
EXECUTE (@string)
IF EXISTS( select * from sysobjects where xtype='U'and name ='zzTemp' )
Begin
Drop table zzTemp
End
DECLARE @ColumnName VARCHAR(200)
DECLARE @ColumnStr VARCHAR(5000)
select @ColumnStr= ''
select @ColumnStr=@ColumnStr + quotename(rtrim(人员编号)) +'float NULL,' from V_Temp
print @ColumnStr
SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1)
SELECT @string = 'CREATE TABLE zzTemp (列名 varchar(50) NULL,' + @ColumnStr + ') ON [PRIMARY]'
print @string
EXECUTE (@string)
--2插入记录
DECLARE Column_cur SCROLL CURSOR FOR
SELECT name FROM syscolumns WHERE ID=object_id(@TableNane) and name <>'人员编号'
OPEN Column_cur
FETCH FIRST FROM Column_cur into @ColumnName
WHILE (@@fetch_status<>-1)
BEGIN
select @ColumnStr= ''
if @ColumnName = '基本工资'
select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(基本工资,0)) + ''',' from V_Temp
else if @ColumnName = '奖金'
select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(奖金,0)) + ''',' from V_Temp
else if @ColumnName = '合计'
select @ColumnStr= @ColumnStr + '''' + convert(varchar(20),ISNULL(合计,0)) + ''',' from V_Temp
SET @ColumnStr = left(@ColumnStr,len(@ColumnStr)-1)
select @string = 'insert into zzTemp values(''' + @ColumnName + ''',' + @ColumnStr +')'
execute(@string)
FETCH NEXT FROM Column_cur into @ColumnName
END
CLOSE Column_cur
DEALLOCATE Column_cur
go
--察看结果
select * from data2
select * from zzTemp
=====================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程
将指定的表,按指定的字段进行行列互换
--邹建 2004.04--*/
/*--使用示例
--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说',38.0,59.2
union all select '散文',18.9,30.6
union all select '哲学',16.2,10.2
/*--要求转换结果
性别 小说 散文 哲学
---- ----- ----- -----
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2
(所影响的行数为 2 行)
--*/
--调用存储过程
exec p_zj '表','类别','性别'
--删除测试
drop table 表
--*/
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+'+'',[''+cast(['+@fdname+'] as varchar)+'']=''''''+replace(['+name+'],'''','''''''')+'''''''' 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
order by colid
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
Oracle's:
本文原创发布php中文网,转载请注明出处,感谢您的尊重!