问下行列转化中max函数的作用

[飘零一叶]

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的作用是什么？本人不是很理解，希望大虾指教。

[华夏小卒]

聚合函数，返回分组中最大的值

[hao1hao2hao3]

取最大值啊！

[永生天地]

你去掉max和group by 查询一看就知道了

declare @s varchar(8000)
set @s='select s.id,s.name'
select @s=@s+',(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'
exec(@s)

[王向飞]

必然会有空值

[xxccy]

聚合，将多行合起来，去除行转列过程中产生的空值

[飘零一叶]

[Quote=引用 3 楼 xys_777 的回复:]
你去掉max和group by 查询一看就知道了


SQL code
declare @s varchar(8000)
set @s='select s.id,s.name'
select @s=@s+',(case when subject='''+subject+''' then VALUE end) '+subject
from Grade group by subjec……
[/Quote]

谢谢 永生哥 小弟明白喽