IF(OBJECT_ID('Student') IS NOT NULL)
DROP TABLE Student
CREATE TABLE Student
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(10)
)
INSERT Student
SELECT '张三' UNION
SELECT '李四' UNION
SELECT '王五'
IF(OBJECT_ID('Grade') IS NOT null)
DROP TABLE Grade
CREATE TABLE Grade
(
SID INT , --对应着 student 中的ID
SUBJECT VARCHAR(50),
VALUE INT,
order_index INT , --在行上的顺寻
flag int --是否显示
)
INSERT grade VALUES(1,'English',66,1,1)
INSERT grade VALUES(1,'Chinese',67,2,1)
INSERT grade VALUES(1,'Math',68,3,1)
INSERT grade VALUES(2,'English',63,1,1)
INSERT grade VALUES(2,'Chinese',64,2,1)
INSERT grade VALUES(2,'Math',65,3,1)
SELECT * FROM Student s
SELECT * FROM Grade
declare @s varchar(8000)
set @s='select s.id,s.name'
select @s=@s+',max(case when subject='''+subject+''' then VALUE end) '+subject
from Grade group by subject order by min(order_index )
set @s=@s+' FROM Student s left join Grade g on s.id=g.sid group by s.id,s.name '
exec(@s)
MAX的作用是什么？本人不是很理解，希望大虾指教。
