首页 新闻 赞助 找找看

sql 查询问题

0
[已关闭问题] 关闭于 2016-05-06 15:54

怎么把上面的表 变成下边的表  如图片

liyanzhao的主页 liyanzhao | 初学一级 | 园豆:42
提问于:2016-05-06 15:30
< >
分享
所有回答(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 DO
SET 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();
追梦少年.T | 园豆:20 (初学一级) | 2016-05-06 15:55
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

 

剧里局外 | 园豆:3 (初学一级) | 2016-05-06 18:18

这样写就对了。

支持(0) 反对(0) chenaran | 园豆:283 (菜鸟二级) | 2016-05-11 15:59
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

徐长卿学数据分析 | 园豆:202 (菜鸟二级) | 2016-10-18 09:16
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册