sql 查询问题

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

liyanzhao | 初学一级 | 园豆：42

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();`

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

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

您需要登录以后才能回答，未注册用户请先注册