怎么用一条sql语句把学生的分数都列出来

Question

数据库如下
name           subject         score
云天河         语文            59
云天河         数学            98
云天河         英语            65
柳梦璃         语文            95
柳梦璃         数学            85
柳梦璃         英语            93
韩菱纱         语文            80
韩菱纱         数学            97
韩菱纱         英语            79 
怎么用一条sql语句查出他们的成绩，结果如下形式：
姓名        语文       数学      英语
云天河      59         98        65
柳梦璃      95         85        93
韩菱纱      80         97        79

Answer 1

通行列转换(version 
2.0
)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：
2008
-
03
-
09
地点：广东深圳说明：普通行列转换(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
 课程
set
 
@sql
 
=
 
'
[
'
 
+
 
@sql
 
+
 
'
]
'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 姓名) nwhere 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 
ascexec
(
@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

Answer 2

select name, 
sum(decode(subject,'语文', score,null)) "语文",
sum(decode(subject,'数学', score,null)) "数学",
sum(decode(subject,'英语', score,null)) "英语"
from testTable t
group by name;

Answer 3

相关子查询法
设你的表名是tablenameselect name,
(select score from tablename where name = t.name and subject = '语文'),
(select score from tablename where name = t.name and subject = '数学'),
(select score from tablename where name = t.name and subject = '英语')
from tablename t
group by name;
你试试看，我没做测试。

Answer 4

select name,sum(case when subject='语文' then score end) as '语文',sum(case when subject='数学' then score end) as '数学',sum(case when subject='英文' then score end) as '英文' from student group by name.
楼上的应该阔以，我的介个应该也阔以吧。你测试测试。

Answer 5

http://apps.hi.baidu.com/share/detail/15986450你可以看看这个,一定有你想要的方法,里面各种解决方法, 我试过了,可以执行的

Answer 6

select name, 
sum(decode(subject,'语文', score,0)) 语文,
sum(decode(subject,'数学', score,0)) 数学,
sum(decode(subject,'英语', score,0)) 英语
from userTable t
group by name;