查询出来的列作为字段名

[insheng1990]

表t1:

F2 F3
A 10
B 5
C 5
D 10
请问怎样用动态sql查询出以下的效果：

A B C D
10 5 5 10


数据库是Mysql5.1 还有列F2的值不是固定的 请问各位大神怎么写啊？

[insheng1990]

自己先顶

[--小F--]

MYSQL 5.1的动态SQL行转列。

[--小F--]

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;

给个例子 自己去研究下

[lichengduo]

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 table1

drop table table1

[insheng1990]

[Quote=引用 4 楼 的回复:]

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 tab……
[/Quote]
你这个是静态的啊，列值会变化的。

[insheng1990]

[Quote=引用 3 楼 的回复:]

SQL code
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'张三',……
[/Quote]
谢谢，不过为什么我按你的动态查询，结果是这样呢？

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)

[insheng1990]

[Quote=引用 7 楼 的回复:]

SQL code

--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='s……
[/Quote]
执行时报错，mysql数据库好像不支持这种写法

[insheng1990]

到底应该怎么写呢