DROP TABLE IF EXISTS CLASS; Create table Class( Student varchar(2), Course varchar(2), Score int); Insert Class select N'张三',N'语文',78 union all select N'张三',N'数学',87 union all select N'张三',N'英语',82 union all select N'张三',N'物理',90 union all select N'李四',N'语文',65 union all select N'李四',N'数学',77 union all select N'李四',N'英语',65 union all select N'李四',N'物理',85 ;-- 静态: SELECT student, max(if(Course='语文',Score,0)) as '语文', max(if(Course='数学',Score,0)) as '数学', max(if(Course='英语',Score,0)) as '英语', max(if(Course='物理',Score,0)) as '物理' FROM class GROUP BY student;-- 动态 SET @S=''; SELECT @S:=CONCAT(@S,'MAX(IF(Course=\'',Course,'\'',',Score,0)) AS ',Course,',') FROM (SELECT DISTINCT Course FROM CLASS) A; SELECT @S; SET @S=CONCAT('SELECT Student,',LEFT(@S,CHAR_LENGTH(@S)-1),' FROM CLASS GROUP BY Student '); SELECT @S; PREPARE stmt FROM @S; EXECUTE stmt;给个例子 自己去研究下
create table table1 ( F2 varchar(20), F3 varchar(20) ) insert into table1 values('A','10') insert into table1 values('B','5')insert into table1 values('C','5')insert into table1 values('D','10') SELECT MAX (CASE WHEN F2 = 'A' THEN F3 ELSE NULL end) as A, MAX (CASE WHEN F2 = 'B' THEN F3 ELSE NULL end ) AS B, MAX (CASE WHEN F2 = 'C' THEN F3 ELSE NULL end )as C, MAX (CASE WHEN F2 = 'D' THEN F3 ELSE NULL end )AS D
FROM table1drop table table1
谢谢,不过为什么我按你的动态查询,结果是这样呢?MAX(IF(Course='语文',Score,0)) AS 语文, MAX(IF(Course='语文',Score,0)) AS 语文,MAX(IF(Course='数学',Score,0)) AS 数学, MAX(IF(Course='语文',Score,0)) AS 语文,MAX(IF(Course='数学',Score,0)) AS 数学,MAX(IF(Course='英语',Score,0)) AS 英语, MAX(IF(Course='语文',Score,0)) AS 语文,MAX(IF(Course='数学',Score,0)) AS 数学,MAX(IF(Course='英语',Score,0)) AS 英语,MAX(IF(Course='物理',Score,0)) AS 物理,
--try declare @sql varchar(8000)select @sql=isnull(@sql+',','')+' max(case F2 when '''+F2+''' then F3 else null end) ['+F2+']'from (select distinct F2 from t1) as a set @sql='select '+@sql+' from t1 group by F2,F3'exec(@sql)
Create table Class(
Student varchar(2),
Course varchar(2),
Score int);
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 ;-- 静态:
SELECT
student,
max(if(Course='语文',Score,0)) as '语文',
max(if(Course='数学',Score,0)) as '数学',
max(if(Course='英语',Score,0)) as '英语',
max(if(Course='物理',Score,0)) as '物理'
FROM
class
GROUP BY
student;-- 动态
SET @S='';
SELECT @S:=CONCAT(@S,'MAX(IF(Course=\'',Course,'\'',',Score,0)) AS ',Course,',') FROM (SELECT DISTINCT Course FROM CLASS) A;
SELECT @S;
SET @S=CONCAT('SELECT Student,',LEFT(@S,CHAR_LENGTH(@S)-1),' FROM CLASS GROUP BY Student ');
SELECT @S;
PREPARE stmt FROM @S;
EXECUTE stmt;给个例子 自己去研究下
(
F2 varchar(20),
F3 varchar(20)
)
insert into table1
values('A','10')
insert into table1
values('B','5')insert into table1
values('C','5')insert into table1
values('D','10')
SELECT
MAX (CASE WHEN F2 = 'A' THEN F3 ELSE NULL end) as A,
MAX (CASE WHEN F2 = 'B' THEN F3 ELSE NULL end ) AS B,
MAX (CASE WHEN F2 = 'C' THEN F3 ELSE NULL end )as C,
MAX (CASE WHEN F2 = 'D' THEN F3 ELSE NULL end )AS D
FROM table1drop table table1
MAX(IF(Course='语文',Score,0)) AS 语文,MAX(IF(Course='数学',Score,0)) AS 数学,
MAX(IF(Course='语文',Score,0)) AS 语文,MAX(IF(Course='数学',Score,0)) AS 数学,MAX(IF(Course='英语',Score,0)) AS 英语,
MAX(IF(Course='语文',Score,0)) AS 语文,MAX(IF(Course='数学',Score,0)) AS 数学,MAX(IF(Course='英语',Score,0)) AS 英语,MAX(IF(Course='物理',Score,0)) AS 物理,
--try
declare @sql varchar(8000)select @sql=isnull(@sql+',','')+' max(case F2 when '''+F2+''' then F3 else null end) ['+F2+']'from (select distinct F2 from t1) as a set @sql='select '+@sql+' from t1 group by F2,F3'exec(@sql)