sql查询语句行转列的高效写法-阿里云开发者社区

开发者社区> 数据库> 正文

sql查询语句行转列的高效写法

简介:

/*

Create Table cj

(

姓名 varchar(10),

学科 varchar(10),

分数 Int

)

INSERT INTO cj (姓名,学科,分数) values (N'张三',N'语文',80)

INSERT INTO cj (姓名,学科,分数) values (N'张三',N'数学',90)

INSERT INTO cj (姓名,学科,分数) values (N'张三',N'外语',85)

INSERT INTO cj (姓名,学科,分数) values (N'李四',N'语文',81)

INSERT INTO cj (姓名,学科,分数) values (N'李四',N'数学',85)

INSERT INTO cj (姓名,学科,分数) values (N'李四',N'外语',90)

INSERT INTO cj (姓名,学科,分数) values (N'李四',N'体育',90)

INSERT INTO cj (姓名,学科,分数) values (N'王五',N'体育',98)

INSERT INTO cj (姓名,学科,分数) values (N'赵六',N'物理',90)

INSERT INTO cj (姓名,学科,分数) values (N'赵六',N'生物',98)

INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'生物',95)

INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'体育',97)

INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'语文',99)

INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'数学',100)

INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'外语',95)

INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'物理',99)

*/

------静态写法(适合学科比较少的)

Select *,语文+数学+外语 As '总分' Into #Tmp From(

Select 姓名,

Sum(CAse 学科 When '语文' Then 分数 Else 0 End) As '语文',

Sum(CAse 学科 When '数学' Then 分数 Else 0 End) As '数学',

Sum(CAse 学科 When '外语' Then 分数 Else 0 End) As '外语'

From cj Group By 姓名) T

Select * From #Tmp Union

Select '总分',Sum(语文),Sum(数学),Sum(外语),Sum(总分) From #Tmp

Drop Table #Tmp

-----动态写法(不管有多少学科都可以,如大学里每个人选修的课程不一样,且课程数量比较多)

Declare @sql varchar(4000)--行转列的SQL

Declare @RightSum varchar(2000)--右边求和的SQL

Declare @BottomSum varchar(2000)--底部求和的SQL

---1、SQL:先把行转成列,并插入一个新表临时表#T1

Set @sql = ' Select * Into #T1 From (Select 姓名,'

Select @sql = @sql + 'Sum(Case 学科 When '''+学科+''' Then 分数 Else 0 End) as '''+学科+''','

From (Select Distinct 学科 From cj) As a

Select @sql = Left(@sql,len(@sql)-1) +' From cj group by 姓名) Y '

---2、SQL:从T1求右边和并插入临时表#T2中

Set @RightSum=' Select *,'

Select @RightSum=@RightSum+''+学科+'+'

From (Select Distinct 学科 From cj) As b

Select @RightSum = Left(@RightSum,len(@RightSum)-1) + ' As ''总分'' Into #T2 From #T1 '

---3、SQL:列出所有及求底部和

Set @BottomSum=' Select * From #T2 Union Select ''总分'','

Select @BottomSum=@BottomSum+'Sum('+学科+'),'

From (Select Distinct 学科 From cj) As b

Select @BottomSum = Left(@BottomSum,len(@BottomSum)-2) + '),sum(总分) From #T2'

---4、执行

Exec(@sql+@RightSum+@BottomSum)---连接顺序:1、@sql 2、@RightSum 3、@BottomSum

---输出查看语句

Pr

int @sql

Print @RightSum

Print @BottomSum

/*

如果要单步执行,则不能用临时表,把#T1、#T2的#去掉即可。这是因为单步执行完成后临时表也就被删除了:

--1、

Exec (@sql)

--2、

Exec(@RightSum)

--3、

Exec(@BottomSum)

--4、执行完后删除表

Drop Table T1

Drop Table T2

建议不要单步执行,因为会产生实体表(T1、T2),如果多人同时访问时就会出问题;而用临时表(#T1、#T2)多人同时访问时互不影响。

*/











本文转自51GT51CTO博客,原文链接:http://blog.51cto.com/yataigp/2062525 ,如需转载请自行联系原作者


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章