迅雷校园面试一道SQL题目

有一表t_Score
name course score
小A 语文 88
小A 数学 87
小A 英语 67
小B 语文 90
小B 数学 86
小B 英语 89
写出合适的SQL，得出下面的结果

姓名 语文 数学 英语

小A 88 87 67
小B 90 86 89

[code="sql"]

select name as 姓名,
sum(case when course='语文' then score end) as 语文,
sum(case when course='数学' then score end) as 数学,
sum(case when course='物理' then score end) as 物理
from t_Score group by name
[/code]
不知道符合你的要求不？

典型的行转列。
使用case when或者decode函数，自己动手写一下就明白了。

select
decode(t.grade_id,1,'一年级',2,'二年级',3,'三年级') 年级,

sum(decode(t.subject_name,’语文’,t.max_score,0)) 语文,
sum(decode(t.subject_name,'数学',t.max_score,0)) 数学,
sum(decode(t.subject_name,'英语',t.max_score,0)) 英语

from
test_table t
group by
t.grade_id
参考一下/

sorry ，那个物理应该写成英语。

[code="sql"]
SELECT n.name,
(
SELECT sum(score) FROM
(
SELECT '小A' NAME,'语文' course, 88 score FROM dual
UNION ALL
SELECT '小A' NAME,'数学' course, 87 score FROM dual
UNION ALL
SELECT '小A' NAME,'英语' course, 67 score FROM dual
UNION ALL
SELECT '小B' NAME,'语文' course, 90 score FROM dual
UNION ALL
SELECT '小B' NAME,'数学' course, 86 score FROM dual
UNION ALL
SELECT '小B' NAME,'英语' course, 89 score FROM dual
) t WHERE t.course='语文' AND t.name=n.name
) 语文,
(
SELECT sum(score) FROM
(
SELECT '小A' NAME,'语文' course, 88 score FROM dual
UNION ALL
SELECT '小A' NAME,'数学' course, 87 score FROM dual
UNION ALL
SELECT '小A' NAME,'英语' course, 67 score FROM dual
UNION ALL
SELECT '小B' NAME,'语文' course, 90 score FROM dual
UNION ALL
SELECT '小B' NAME,'数学' course, 86 score FROM dual
UNION ALL
SELECT '小B' NAME,'英语' course, 89 score FROM dual
) t WHERE t.course='数学' AND t.name=n.name
) 数学,
(
SELECT sum(score) FROM
(
SELECT '小A' NAME,'语文' course, 88 score FROM dual
UNION ALL
SELECT '小A' NAME,'数学' course, 87 score FROM dual
UNION ALL
SELECT '小A' NAME,'英语' course, 67 score FROM dual
UNION ALL
SELECT '小B' NAME,'语文' course, 90 score FROM dual
UNION ALL
SELECT '小B' NAME,'数学' course, 86 score FROM dual
UNION ALL
SELECT '小B' NAME,'英语' course, 89 score FROM dual
) t WHERE t.course='英语' AND t.name=n.name
) 英语
FROM
(
SELECT NAME
FROM (
SELECT '小A' NAME,'语文' course, 88 score FROM dual
UNION ALL
SELECT '小A' NAME,'数学' course, 87 score FROM dual
UNION ALL
SELECT '小A' NAME,'英语' course, 67 score FROM dual
UNION ALL
SELECT '小B' NAME,'语文' course, 90 score FROM dual
UNION ALL
SELECT '小B' NAME,'数学' course, 86 score FROM dual
UNION ALL
SELECT '小B' NAME,'英语' course, 89 score FROM dual )
GROUP BY NAME ) n
[/code]
:D 气死面试官

这样应该也可以吧，先查出语，数，英这三个集合的数据，然后再用name连起来，如：

[code="sql"]select T.name,YW.yuwen as' 语文',SX.shuxue as '数学', YY.yingyu as '英语'
from t_Score T,
(select name,score as yuwen from t_Score where course='语文') YW,
(select name,score as shuxue from t_Score where course='数学') SX,
(select name,score as yingyu from t_Score where course='英语') YY
where T.name = YW.name and T.name=SX.name and T.name=YY.name[/code]