参考: /* 问题:如果上述两表互相换一下:即表结构和数据为: 姓名 语文 数学 物理 张三 74 83 93 李四 74 84 94 想变成(得到如下结果): 姓名 课程 分数 ---- ---- ---- 李四 语文 74 李四 数学 84 李四 物理 94 张三 语文 74 张三 数学 83 张三 物理 93 -------------- */create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int) insert into tb values('张三',74,83,93) insert into tb values('李四',74,84,94) go--SQL SERVER 2000 静态SQL。 select * from ( select 姓名 , 课程 = '语文' , 分数 = 语文 from tb union all select 姓名 , 课程 = '数学' , 分数 = 数学 from tb union all select 姓名 , 课程 = '物理' , 分数 = 物理 from tb ) t order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。 --调用系统表动态生态。 declare @sql varchar(8000) select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb' from syscolumns where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列 order by colid asc exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。 select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。-------------------- /* 问题:在上述的结果上加个平均分,总分,得到如下结果: 姓名 课程 分数 ---- ------ ------ 李四 语文 74.00 李四 数学 84.00 李四 物理 94.00 李四 平均分 84.00 李四 总分 252.00 张三 语文 74.00 张三 数学 83.00 张三 物理 93.00 张三 平均分 83.33 张三 总分 250.00 ------------------ */select * from ( select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb union all select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb union all select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb union all select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb union all select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb ) t order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb --按某一字段分组取最大(小)值所在行的数据 --(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州) /* 数据如下: name val memo a 2 a2(a的第二个值) a 1 a1--a的第一个值 a 3 a3:a的第三个值 b 1 b1--b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ --创建表并插入数据: create table tb(name varchar(10),val int,memo varchar(20)) insert into tb values('a', 2, 'a2(a的第二个值)') insert into tb values('a', 1, 'a1--a的第一个值') insert into tb values('a', 3, 'a3:a的第三个值') insert into tb values('b', 1, 'b1--b的第一个值') insert into tb values('b', 3, 'b3:b的第三个值') insert into tb values('b', 2, 'b2b2b2b2') insert into tb values('b', 4, 'b4b4') insert into tb values('b', 5, 'b5b5b5b5b5') go--一、按name分组取val最大的值所在行的数据。 --方法1: select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name --方法2: select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val) --方法3: select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name --方法4: select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name --方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name /* name val memo ---------- ----------- -------------------- a 3 a3:a的第三个值 b 5 b5b5b5b5b5 */--二、按name分组取val最小的值所在行的数据。 --方法1: select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name --方法2: select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val) --方法3: select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name --方法4: select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name --方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 */--三、按name分组取第一次出现的行所在的数据。 select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name /* name val memo ---------- ----------- -------------------- a 2 a2(a的第二个值) b 1 b1--b的第一个值 */
--行列转换示例--列变行--测试数据 create table 表(id int,proc1 decimal(20,1),proc2 decimal(20,1),proc3 decimal(20,1)) insert 表 select 12,3.4,6.7,1.1 union all select 13,5.6,10.3,5.6 union all select 14,7.9,9.0,9.9 go--查询处理 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+'=''select id,[proc]='''''+name+''''',value=['+name+'] from 表''' ,@s3=@s3+'+'' union all ''+@'+@i ,@i=cast(@i as int)+1 from syscolumns where object_id('表')=id and name<>'id'select @s1=substring(@s1,2,8000) ,@s2=substring(@s2,2,8000) ,@s3=substring(@s3,16,8000)exec('declare '+@s1+' select '+@s2+' exec(''select * from(''+'+@s3+'+'')a order by id'')') go --删除测试 drop table 表/*--测试结果id proc value ----------- ----- ---------------------- 12 proc1 3.4 12 proc2 6.7 12 proc3 1.1 13 proc3 5.6 13 proc2 10.3 13 proc1 5.6 14 proc1 7.9 14 proc2 9.0 14 proc3 9.9(所影响的行数为 9 行) --*/
/* 问题:如果上述两表互相换一下:即表结构和数据为: 姓名 语文 数学 物理 张三 74 83 93 李四 74 84 94 想变成(得到如下结果): 姓名 课程 分数 ---- ---- ---- 李四 语文 74 李四 数学 84 李四 物理 94 张三 语文 74 张三 数学 83 张三 物理 93 -------------- */create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int) insert into tb values('张三',74,83,93) insert into tb values('李四',74,84,94) go--SQL SERVER 2000 静态SQL。 select * from ( select 姓名 , 课程 = '语文' , 分数 = 语文 from tb union all select 姓名 , 课程 = '数学' , 分数 = 数学 from tb union all select 姓名 , 课程 = '物理' , 分数 = 物理 from tb ) t order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。 --调用系统表动态生态。 declare @sql varchar(8000) select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb' from syscolumns where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列 order by colid asc exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。 select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。
select col='A',col2 = A from table1 union all select col='B',col2 = B from table1 union all select col='C',col2 = C from table1 union all select col='D',col2 = D from table1
create table tb(a int,b int,c int,d int)insert tb values(5, 7, 2, 8 )select ColumnName,Value from (select a,b,c,d from tb) p unpivot (value for ColumnName in(a,b,c,d)) up /* ColumnName Value --------------- ----------- a 5 b 7 c 2 d 8 */
declare @T table(A int, B int, C int, D int) insert into @T select 5, 7, 2, 8 select col='A',col2 = A from @T union all select col='B',col2 = B from @T union all select col='C',col2 = C from @T union all select col='D',col2 = D from @T--A 5 --B 7 --C 2 --D 8
用union好像不好,如果列名动态的怎么办
declare @tb table (id int,A int,B int,C int,D int) insert into @tb select 1,5,7,2,8 select 序号,等级 from @tb a unpivot (等级 for 序号 in (A ,B,C,D)) b(1 行受影响) 序号 等级 -------------------------------------------------------------------------------------------------------------------------------- ----------- A 5 B 7 C 2 D 8(4 行受影响)
create table tb(a int,b int,c int,d int)insert tb values(5, 7, 2, 8 )select ColumnName,Value from (select a,b,c,d from tb) p unpivot (value for ColumnName in(a,b,c,d)) up /* ColumnName Value --------------- ----------- a 5 b 7 c 2 d 8 */楼上强悍
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。--------------------
/*
问题:在上述的结果上加个平均分,总分,得到如下结果:
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb
--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
*/
create table 表(id int,proc1 decimal(20,1),proc2 decimal(20,1),proc3 decimal(20,1))
insert 表 select 12,3.4,6.7,1.1
union all select 13,5.6,10.3,5.6
union all select 14,7.9,9.0,9.9
go--查询处理
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+'=''select id,[proc]='''''+name+''''',value=['+name+'] from 表'''
,@s3=@s3+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('表')=id and name<>'id'select @s1=substring(@s1,2,8000)
,@s2=substring(@s2,2,8000)
,@s3=substring(@s3,16,8000)exec('declare '+@s1+'
select '+@s2+'
exec(''select * from(''+'+@s3+'+'')a order by id'')')
go
--删除测试
drop table 表/*--测试结果id proc value
----------- ----- ----------------------
12 proc1 3.4
12 proc2 6.7
12 proc3 1.1
13 proc3 5.6
13 proc2 10.3
13 proc1 5.6
14 proc1 7.9
14 proc2 9.0
14 proc3 9.9(所影响的行数为 9 行)
--*/
/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。
select col='A',col2 = A from table1
union all
select col='B',col2 = B from table1
union all
select col='C',col2 = C from table1
union all
select col='D',col2 = D from table1
(select a,b,c,d from tb) p
unpivot
(value for ColumnName in(a,b,c,d)) up
/*
ColumnName Value
--------------- -----------
a 5
b 7
c 2
d 8
*/
declare @T table(A int, B int, C int, D int)
insert into @T
select 5, 7, 2, 8 select col='A',col2 = A from @T
union all
select col='B',col2 = B from @T
union all
select col='C',col2 = C from @T
union all
select col='D',col2 = D from @T--A 5
--B 7
--C 2
--D 8
insert into @tb select 1,5,7,2,8
select 序号,等级 from @tb a unpivot (等级 for 序号 in (A ,B,C,D)) b(1 行受影响)
序号 等级
-------------------------------------------------------------------------------------------------------------------------------- -----------
A 5
B 7
C 2
D 8(4 行受影响)
create table tb(a int,b int,c int,d int)insert tb values(5, 7, 2, 8 )select ColumnName,Value from
(select a,b,c,d from tb) p
unpivot
(value for ColumnName in(a,b,c,d)) up
/*
ColumnName Value
--------------- -----------
a 5
b 7
c 2
d 8
*/楼上强悍