sql 查询问题
0
[已关闭问题]
关闭于 2016-05-06 15:54
怎么把上面的表 变成下边的表 如图片 
所有回答(3)
0
DELIMITER &&CREATE PROCEDURE sp_count()BEGIN#课程名称DECLARE course_n VARCHAR(20);#所有课程数量DECLARE count INT;#计数器DECLARE i INT DEFAULT 0;#拼接SQL字符串SET @s = 'SELECT name';SET count = (SELECT COUNT(distinct course) FROM grade);WHILE i < count DOSET course_n = (SELECT course FROM grade LIMIT i,1);SET @s = CONCAT(@s, ', SUM(CASE course WHEN ','\'', course_n,'\'',' THEN score END )',' AS ','\'',course_n,'\'');SET i = i+1;END WHILE;SET @s = CONCAT(@s, ' FROM grade GROUP BY name');#用于调试#SELECT @s;PREPARE stmt FROM @s;EXECUTE stmt;END&& call sp_count();
1
select s.name, sum(s.yuwen) as yuwen ,sum(s.shuxue)as shuxue ,sum(s.yingyu) as yingyu from(
select name,
case course
when '语文' then score else 0 end as 'yuwen',
case course
when '数学' then score else 0 end as 'shuxue',
case course
when '英语' then score else 0 end as 'yingyu'
from dbo.Student2 )s group by s.name
这样写就对了。
0
使用sql的透视表函数：pivot
IF OBJECT_ID('stu') IS NOT NULL
DROP TABLE stu
CREATE TABLE stu (name NVARCHAR(10),kecheng NVARCHAR(10),fenshu INT)
INSERT INTO stu
SELECT '张三','英语',81 UNION all
SELECT '张三','数学',75 UNION all
SELECT '李四','语文',76 UNION all
SELECT '李四','数学',90 UNION all
SELECT '王五','语文',81 UNION all
SELECT '王五','数学',100 UNION all
SELECT '王五','英语',90
SELECT DISTINCT kecheng FROM stu
SELECT name ,[数学],[英语],[语文]
FROM stu
pivot
(
sum(fenshu)
for kecheng in
([数学],[英语],[语文])
)
as pvt
