34,591
社区成员
发帖
与我相关
我的任务
分享一个SQL语句的写法
表A
name course score
---------------------------------------------------------------
张三 语文 70
张三 语文 80
张三 数学 90
张三 数学 80
张三 英语 90
张三 英语 70
李四 语文 60
李四 语文 80
李四 数学 90
李四 数学 50
李四 英语 80
李四 英语 70
怎样转换为
表B
name 语文 数学 英语
--------------------------------
张三 150 170 160
李四 140 140 150
name course score
---------------------------------------------------------------
张三 语文 70
张三 语文 80
张三 数学 90
张三 数学 80
张三 英语 90
张三 英语 70
李四 语文 60
李四 语文 80
李四 数学 90
李四 数学 50
李四 英语 80
李四 英语 70
怎样转换为
表B
name 语文 数学 英语
--------------------------------
张三 150 170 160
李四 140 140 150
...全文
请发表友善的回复…
发表回复
pengpeng409 2012-05-14
- 打赏
- 举报
if object_id('tempdb..#tb') is not null
drop table #tb
create table #tb
(
tname varchar(20),
course varchar(20),
score int
)
insert into #tb(tname,course,score)
select '张三','语文',70
union all
select '张三','语文',80
union all
select '张三','数学',90
union all
select '张三','数学',80
union all
select '张三','英语',90
union all
select '张三','英语',80
select tname ,sum(case when course='语文' then score else 0 end) '语文',
sum(case when course='数学' then score else 0 end) '数学',
sum(case when course='英语' then score else 0 end) '英语'
from #tb
group by tname
--(1 行受影响)
select * from #tb
drop table #tb
create table #tb
(
tname varchar(20),
course varchar(20),
score int
)
insert into #tb(tname,course,score)
select '张三','语文',70
union all
select '张三','语文',80
union all
select '张三','数学',90
union all
select '张三','数学',80
union all
select '张三','英语',90
union all
select '张三','英语',80
select tname ,sum(case when course='语文' then score else 0 end) '语文',
sum(case when course='数学' then score else 0 end) '数学',
sum(case when course='英语' then score else 0 end) '英语'
from #tb
group by tname
--(1 行受影响)
select * from #tb
三岁 2012-05-14
- 打赏
- 举报
我没分了。想占点便宜啊。大侠们像这样的SQL语句格式有木有实例啊。给一个嘛。非常感谢了。我QQ429592913
格式：
艺术0-1000 艺术1000以上 婚5999以下 6000以上 10000以上 HT-3999以下
个 金额 个 金额 个 金额 个 金额 个 金额 个 金额
有实例的可以给我一个真的非常谢谢了
格式：
艺术0-1000 艺术1000以上 婚5999以下 6000以上 10000以上 HT-3999以下
个 金额 个 金额 个 金额 个 金额 个 金额 个 金额
有实例的可以给我一个真的非常谢谢了
liu2343598 2012-05-14
- 打赏
- 举报
如果是我的话，我会选择写函数，先选出所有用户，然后根据该用户分别查询成绩，不知道这样写怎么样？
我腫了 2012-05-14
- 打赏
- 举报
--表B
--name 语文 数学 英语
----------------------------------
--张三 150 170 160
--李四 140 140 150
if object_id('A') is not null
drop table A
Go
Create table A([name] nvarchar(2),[course] nvarchar(2),[score] smallint)
Insert into A
Select N'张三',N'语文',70
Union all Select N'张三',N'语文',80
Union all Select N'张三',N'数学',90
Union all Select N'张三',N'数学',80
Union all Select N'张三',N'英语',90
Union all Select N'张三',N'英语',70
Union all Select N'李四',N'语文',60
Union all Select N'李四',N'语文',80
Union all Select N'李四',N'数学',90
Union all Select N'李四',N'数学',50
Union all Select N'李四',N'英语',80
Union all Select N'李四',N'英语',70
;WITH cte AS (
select name,course,SUM(score) AS score from A
GROUP BY name,course
)
SELECT name,语文,数学,英语 FROM cte
PIVOT (
max(score) FOR course IN(语文,数学,英语)
) AS p
Go
ChinaITOldMan 2012-05-13
- 打赏
- 举报
row to column, old topic
wangxiaofeiwuqiao 2012-05-12
- 打赏
- 举报
好熟悉的行变列。学习
sunspringsd 2012-05-12
- 打赏
- 举报
楼上说的非常详细,学习了
m_y_y_g 2012-05-12
- 打赏
- 举报
<font face="Arial" size=2>
<p>Microsoft OLE DB Provider for ODBC Drivers</font> <font face="Arial" size=2>error '80040e14'</font>
<p>
<font face="Arial" size=2>[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'tblGrp_Users'.</font>
<p>
<font face="Arial" size=2>/arabic-site/news/viewMobileDetails.asp</font><font face="Arial" size=2>, line 22</font>
这事返回的所有的。。。求指导。。求教育。。
我用的语句是
and (select top 1 name from (select top __i__ name,id from <dbname>..syscolumns where id=object_id('<tablename>') order by name) t order by name desc)=0
and (select col_name(object_id('<tablename>'),__i__))=0
积分不够了、、、、
<p>Microsoft OLE DB Provider for ODBC Drivers</font> <font face="Arial" size=2>error '80040e14'</font>
<p>
<font face="Arial" size=2>[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'tblGrp_Users'.</font>
<p>
<font face="Arial" size=2>/arabic-site/news/viewMobileDetails.asp</font><font face="Arial" size=2>, line 22</font>
这事返回的所有的。。。求指导。。求教育。。
我用的语句是
and (select top 1 name from (select top __i__ name,id from <dbname>..syscolumns where id=object_id('<tablename>') order by name) t order by name desc)=0
and (select col_name(object_id('<tablename>'),__i__))=0
积分不够了、、、、
amloss 2012-05-11
- 打赏
- 举报
标题：普通行列转换(version 2.0)
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 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 end
drop table tb
--> 生成测试数据: #DB_info
if object_id('tempdb.dbo.#DB_info') is not null drop table #DB_info
create table #DB_info (sid int,name nvarchar(4),sex nvarchar(2))
insert into #DB_info
select 1,'李明','男' union all
select 2,'王军','男' union all
select 3,'李敏','女'
--> 生成测试数据: #db_scores
if object_id('tempdb.dbo.#db_scores') is not null drop table #db_scores
create table #db_scores (sid int,type nvarchar(4),scores int)
insert into #db_scores
select 1,'语文',80 union all
select 1,'数学',90 union all
select 2,'语文',85 union all
select 2,'数学',90 union all
select 3,'语文',75 union all
select 3,'数学',85
declare @sql nvarchar(4000)
set @sql='select a.sid,a.name,a.sex'
select @sql=@sql+',max(case when b.type='''+type+''' then b.scores else 0 end) ['+type+']'
from (select distinct type from #db_scores) t
exec (@sql+' from #DB_info a left outer join #db_scores b on a.sid=b.sid group by a.sid,a.name,a.sex')
/*
sid name sex 数学 语文
----------- ---- ---- ----------- -----------
1 李明 男 90 80
2 王军 男 90 85
3 李敏 女 85 75
(3 行受影响)
*/
这个是网上的例子，希望对你有帮助，我以前用到的时候找的
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 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 end
drop table tb
--> 生成测试数据: #DB_info
if object_id('tempdb.dbo.#DB_info') is not null drop table #DB_info
create table #DB_info (sid int,name nvarchar(4),sex nvarchar(2))
insert into #DB_info
select 1,'李明','男' union all
select 2,'王军','男' union all
select 3,'李敏','女'
--> 生成测试数据: #db_scores
if object_id('tempdb.dbo.#db_scores') is not null drop table #db_scores
create table #db_scores (sid int,type nvarchar(4),scores int)
insert into #db_scores
select 1,'语文',80 union all
select 1,'数学',90 union all
select 2,'语文',85 union all
select 2,'数学',90 union all
select 3,'语文',75 union all
select 3,'数学',85
declare @sql nvarchar(4000)
set @sql='select a.sid,a.name,a.sex'
select @sql=@sql+',max(case when b.type='''+type+''' then b.scores else 0 end) ['+type+']'
from (select distinct type from #db_scores) t
exec (@sql+' from #DB_info a left outer join #db_scores b on a.sid=b.sid group by a.sid,a.name,a.sex')
/*
sid name sex 数学 语文
----------- ---- ---- ----------- -----------
1 李明 男 90 80
2 王军 男 90 85
3 李敏 女 85 75
(3 行受影响)
*/
这个是网上的例子，希望对你有帮助，我以前用到的时候找的
LoveYouSelf 2012-05-11
- 打赏
- 举报
[Quote=引用 6 楼 的回复:]
我觉得楼上的方法和我想的一样！
[/Quote]
我发现我没理解楼主的意思！ 楼主要求行和列互换，所以我们错了！ 下面是我重新更正后的sql代码！
我觉得楼上的方法和我想的一样！
[/Quote]
我发现我没理解楼主的意思！ 楼主要求行和列互换，所以我们错了！ 下面是我重新更正后的sql代码！
LoveYouSelf 2012-05-11
- 打赏
- 举报
我的做法比较复杂！
--创建一个数据库来存放原始数据
create database testcs
on
(name = 'testcs',filename = 'F:\fyj\sql serve 2005\database\testcs.mdf')
log on
(name = 'testcs_log',filename = 'F:\fyj\sql serve 2005\database\testcs_log.ldf')
--创建一张表来存放你的原始数据
use testcs
create table sc(cname varchar(20),course varchar(10),grade int)
alter table sc alter column cname
insert into sc(cname,course,grade)
values('李四','英语','70')
--。。。这里有12条记录 我就不列出来了，我全部都插入进去了
--创建一张临时表 专门用来保存张三的信息
create table sctemp(cname varchar(20),course varchar(10),grade int)
insert into sctemp
select * from sc where cname = '张三'
--创建一张临时表 用来保存李四的信息
create table sctemp1(cname varchar(20),course varchar(10),grade int)
insert into sctemp1
select * from sc where cname = '李四'
--将SC表的信息清空
delete from sc
--利用group by和sum函数对张三信息处理后插入sc表
insert into sc
select cname,course, sum(grade) as '总成绩' from sctemp group by course,course,cname
--同理将李四的信息处理插入SC表
insert into sc
select cname,course, sum(grade) as '总成绩' from sctemp1 group by course,course,cname
--最后将sctemp表 和 sctemp1表删除
drop table sctemp
drop table sctemp1
--这个不理解的话 就照下面的子查询运行一遍就知道了
select grade1 '数学' ,grade2 '英语',grade3 '语文'
from
(select * from
(select a.cname cname1,a.course course1,a.grade grade1,b.cname cname2,b.course course2,b.grade grade2,c.cname cname3,c.course course3,c.grade grade3
from sc as a,sc as b, sc as c where a.cname = b.cname and b.cname = c.cname)temp
where temp.course1<>temp.course2 and temp.course2<>temp.course3 and temp.course1<>temp.course3)temp1
where course1 = '数学' and course2 = '英语'
--将以上的三个子查询分别运行一次你就明白了
temp (select a.cname cname1,a.course course1,a.grade grade1,b.cname cname2,b.course course2,b.grade grade2,c.cname cname3,c.course course3,c.grade grade3
from sc as a,sc as b, sc as c where a.cname = b.cname and b.cname = c.cname)
temp1 select * from temp
where temp.course1<>temp.course2 and temp.course2<>temp.course3 and temp.course1<>temp.course3)
select grade1 '数学' ,grade2 '英语',grade3 '语文' from temp1
where course1 = '数学' and course2 = '英语'
LoveYouSelf 2012-05-11
- 打赏
- 举报
[Quote=引用 5 楼 的回复:]
SQL code
--楼上的解法我是没看懂
--我的做法是将张三和李四的成绩分开
--将他们的纪录分别查到两张表里
--在对两张表里的数据利用group by句子和sum来求和
--最后将两张表的查询结果插入一张表。
--OK
--寝室11点断网 没时间发SQL代码了。
[/Quote]
不好意思，没看明白，要将列变成行啊！
SQL code
--楼上的解法我是没看懂
--我的做法是将张三和李四的成绩分开
--将他们的纪录分别查到两张表里
--在对两张表里的数据利用group by句子和sum来求和
--最后将两张表的查询结果插入一张表。
--OK
--寝室11点断网 没时间发SQL代码了。
[/Quote]
不好意思，没看明白，要将列变成行啊！
ritobashi 2012-05-11
- 打赏
- 举报
我觉得楼上的方法和我想的一样！
tyhjx 2012-05-11
- 打赏
- 举报
[Quote=引用 4 楼 的回复:]
select name ,SUM(case when course ='语文' then score else 0 end ) as '语文',
SUM(case when course ='数学' then score else 0 end ) as '数学',
SUM(case when course ='英语' then score else 0 end ) as '英语'
from TB
group by name [/Quote]
++
select name ,SUM(case when course ='语文' then score else 0 end ) as '语文',
SUM(case when course ='数学' then score else 0 end ) as '数学',
SUM(case when course ='英语' then score else 0 end ) as '英语'
from TB
group by name [/Quote]
++
gogodiy 2012-05-11
- 打赏
- 举报
思路就是分两步走：第一步先SUM（score），第二步行转列。MSSQL2005及以上版本：
create table t1
(
name varchar(10),
course varchar(10),
score int
)
insert into t1
select '张三', '语文', 70 union all
select '张三', '语文', 80 union all
select '张三', '数学', 90 union all
select '张三', '数学', 80 union all
select '张三', '英语', 90 union all
select '张三', '英语', 70 union all
select '李四', '语文', 60 union all
select '李四', '语文', 80 union all
select '李四', '数学', 90 union all
select '李四', '数学', 50 union all
select '李四', '英语', 80 union all
select '李四', '英语', 70
select * from t1
;with aaa as
(
select name,course,SUM(score) as score
from t1
group by name,course
)
select name,
MAX(case when course='语文' then score end) as '语文',
MAX(case when course='数学' then score end) as '数学',
MAX(case when course='英语' then score end) as '英语'
from aaa
group by name
order by name desc
------------------------
name 语文 数学 英语
张三 150 170 160
李四 140 140 150
create table t1
(
name varchar(10),
course varchar(10),
score int
)
insert into t1
select '张三', '语文', 70 union all
select '张三', '语文', 80 union all
select '张三', '数学', 90 union all
select '张三', '数学', 80 union all
select '张三', '英语', 90 union all
select '张三', '英语', 70 union all
select '李四', '语文', 60 union all
select '李四', '语文', 80 union all
select '李四', '数学', 90 union all
select '李四', '数学', 50 union all
select '李四', '英语', 80 union all
select '李四', '英语', 70
select * from t1
;with aaa as
(
select name,course,SUM(score) as score
from t1
group by name,course
)
select name,
MAX(case when course='语文' then score end) as '语文',
MAX(case when course='数学' then score end) as '数学',
MAX(case when course='英语' then score end) as '英语'
from aaa
group by name
order by name desc
------------------------
name 语文 数学 英语
张三 150 170 160
李四 140 140 150
LoveYouSelf 2012-05-10
- 打赏
- 举报
--楼上的解法我是没看懂
--我的做法是将张三和李四的成绩分开
--将他们的纪录分别查到两张表里
--在对两张表里的数据利用group by句子和sum来求和
--最后将两张表的查询结果插入一张表。
--OK
--寝室11点断网 没时间发SQL代码了。
Mr_Nice 2012-05-10
- 打赏
- 举报
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (name nvarchar(4),course nvarchar(4),score int)
insert into [tb]
select '张三','语文',70 union all
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','数学',80 union all
select '张三','英语',90 union all
select '张三','英语',70 union all
select '李四','语文',60 union all
select '李四','语文',80 union all
select '李四','数学',90 union all
select '李四','数学',50 union all
select '李四','英语',80 union all
select '李四','英语',70
select * from [tb]
select name ,SUM(case when course ='语文' then score else 0 end ) as '语文',
SUM(case when course ='数学' then score else 0 end ) as '数学',
SUM(case when course ='英语' then score else 0 end ) as '英语'
from TB
group by name
/*
name 语文 数学 英语
---- ----------- ----------- -----------
李四 140 140 150
张三 150 170 160
(2 行受影响)
唐诗三百首 2012-05-10
- 打赏
- 举报
create table 表A
(name varchar(8), course varchar(6), score int)
insert into 表A
select '张三', '语文', 70 union all
select '张三', '语文', 80 union all
select '张三', '数学', 90 union all
select '张三', '数学', 80 union all
select '张三', '英语', 90 union all
select '张三', '英语', 70 union all
select '李四', '语文', 60 union all
select '李四', '语文', 80 union all
select '李四', '数学', 90 union all
select '李四', '数学', 50 union all
select '李四', '英语', 80 union all
select '李四', '英语', 70
select name,[语文],[数学],[英语]
from
(select name,course,sum(score) score
from 表A group by name,course) t1
pivot(max(score) for course in([语文],[数学],[英语])) t2
order by name desc
name 语文 数学 英语
-------- ----------- ----------- -----------
张三 150 170 160
李四 140 140 150
(2 row(s) affected)
开着拖拉机泡妞 2012-05-10
- 打赏
- 举报
--> 测试数据：[test]
go
if object_id('[test]') is not null
drop table [test]
go
create table [test](
[name] varchar(4),
[course] varchar(4),
[score] int
)
go
insert [test]
select '张三','语文',70 union all
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','数学',80 union all
select '张三','英语',90 union all
select '张三','英语',70 union all
select '李四','语文',60 union all
select '李四','语文',80 union all
select '李四','数学',90 union all
select '李四','数学',50 union all
select '李四','英语',80 union all
select '李四','英语',70
--2000动态转换
declare @str varchar(2000)
set @str=''
select
@str=@str+','+[course]+'=max(case when [course]='
+''''+[course]+''''+' then [score] else 0 end)'
from
[test]
group by
[course]
exec('select [name] 姓名'+@str+' from [test] group by [name]')
/*
姓名 数学 英语 语文
李四 90 80 80
张三 90 90 80
*/
--2005以上版本
select
*
from
[test]
pivot
(max([score]) for [course] in([数学],[英语],[语文]))b
/*
name 数学 英语 语文
李四 90 80 80
张三 90 90 80
*/
开着拖拉机泡妞 2012-05-10
- 打赏
- 举报
--行列互转
--摘自中国风博客，引用请标明内容来源
--1、行换列
if object_id('Class') is not 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]
--select @s
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]
select @s
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),
[总成绩]=([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、列转行
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
*/
