请教：如何将查询结果从纵向变成横向?

[wcj1981]

数据库mysql

从a表查询出来的结果，当做b表查询时使用的查询字段，这个sql程序怎么写？

比如，

select en from a where a.en='stuff'

假设从a表查询出来para1,para2

b表就是

select para1,para2 from b

[ACMAIN_CHM]

(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html

1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)

这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

[wcj1981]

CREATE TABLE  datarange (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  endataName varchar(45) NOT NULL ,
  tblname varchar(20) NOT NULL ,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


insert into datarange (id,endataname,tblname) values (1,tk101,'stuff');
insert into datarange (id,endataname,tblname) values (2,tk102,'stuff');
insert into datarange (id,endataname,tblname) values (3,tk103,'stuff');
insert into datarange (id,endataname,tblname) values (4,tk104,'stuffcheck');
insert into datarange (id,endataname,tblname) values (5,tk105,'stuffcheck');

CREATE TABLE  stuff (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  tk101 float DEFAULT NULL,
  tk102 float DEFAULT NULL,
  tk103 float DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


insert into stuff ( id,tk101,tk102,tk103)values (1,80,40,45);
insert into stuff ( id,tk101,tk102,tk103)values (2,30,38,41);
insert into stuff ( id,tk101,tk102,tk103)values (3,7,55,88);

我想得到如下的查询效果：

select endataname from datarange where tblname='stuff';

结果应该是

tk101
tk102
tk103

然后利用从datarange表中查询出的结果，做如下查询:

select tk101,tk102,tk103 from stuff;

请问要是实现这样的查询，语句应该怎么写？

我想我这次是说明白了。

[ACMAIN_CHM]

mysql> CREATE TABLE  datarange (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   endataName varchar(45) NOT NULL ,
    ->   tblname varchar(20) NOT NULL ,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into datarange (id,endataname,tblname) values (1,tk101,'stuff');
ERROR 1054 (42S22): Unknown column 'tk101' in 'field list'
mysql> insert into datarange (id,endataname,tblname) values (2,tk102,'stuff');
ERROR 1054 (42S22): Unknown column 'tk102' in 'field list'
mysql> insert into datarange (id,endataname,tblname) values (3,tk103,'stuff');
ERROR 1054 (42S22): Unknown column 'tk103' in 'field list'
mysql> insert into datarange (id,endataname,tblname) values (4,tk104,'stuffcheck
');
ERROR 1054 (42S22): Unknown column 'tk104' in 'field list'
mysql> insert into datarange (id,endataname,tblname) values (5,tk105,'stuffcheck
');
ERROR 1054 (42S22): Unknown column 'tk105' in 'field list'
mysql>

这就是你的测试数据？！

[mysqi]

[Quote=引用楼主 wcj1981 的回复:]
数据库mysql

从a表查询出来的结果，当做b表查询时使用的查询字段，这个sql程序怎么写？

比如，



SQL code
select en from a where a.en='stuff'

假设从a表查询出来para1,para2

b表就是

SQL code
select para1,para2 from b
[/Quote]
Try:

SET @sql=(SELECT en FROM a WHERE a.en='stuff');
SET @sql=CONCAT('SELECT ',@sql,' FROM b');
PREPARE stmt FROM @sql;
EXECUTE stmt;

[mysqi]

[Quote=引用 4 楼 mysqi 的回复:]
引用楼主 wcj1981 的回复:
数据库mysql

从a表查询出来的结果，当做b表查询时使用的查询字段，这个sql程序怎么写？

比如，



SQL code
select en from a where a.en='stuff'

假设从a表查询出来para1,para2

b表就是

SQL code
select para1,para2 from b
……
[/Quote]
Sorry, revised:

SET @sql=(SELECT GROUP_CONCAT(en)[code=SQL]

FROM a WHERE a.en='stuff');
SET @sql=CONCAT('SELECT ',@sql,' FROM b');
PREPARE stmt FROM @sql;
EXECUTE stmt;
[/code]

[mysqi]

faint:

SET @sql=(SELECT GROUP_CONCAT(en)[code=SQL]

FROM a WHERE a.en='stuff');
SET @sql=CONCAT('SELECT ',@sql,' FROM b');
PREPARE stmt FROM @sql;
EXECUTE stmt;
[/code]

[wcj1981]

to ACMAIN_CHM:不好意思，忘了加引号。

insert into datarange (id,endataname,tblname) values (1,'tk101','stuff');
insert into datarange (id,endataname,tblname) values (2,'tk102','stuff');
insert into datarange (id,endataname,tblname) values (3,'tk103','stuff');
insert into datarange (id,endataname,tblname) values (4,'tk104','stuffcheck');
insert into datarange (id,endataname,tblname) values (5,'tk105','stuffcheck');

[wcj1981]

to mysqi:

执行你写的语句后，出现如下这种错误。
Unknown prepared statement handler (stmt) given to EXECUTE

[WWWWA]

要用SP
DELIMITER $$
DROP PROCEDURE IF EXISTS `testa`.`rowtocol`$$
CREATE PROCEDURE `testa`.`rowtocol`()
BEGIN
DECLARE asql VARCHAR(500);
DECLARE done INT DEFAULT 0;
DECLARE V_A VARCHAR(500);
DECLARE cur1 CURSOR FOR SELECT endataname FROM datarange WHERE tblname='stuff';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET ASQL='select ';
OPEN cur1;
FETCH cur1 INTO v_a;
WHILE done=0 DO
SET asql=CONCAT(asql,v_A,',');
FETCH cur1 INTO v_a;
END WHILE;
SET ASQL=CONCAT(LEFT(ASQL,LENGTH(ASQL)-1),' FROM stuff');
SELECT ASQL;
SET @ASQL1=ASQL;
PREPARE BB FROM @ASQL1;
EXECUTE BB;
END$$

DELIMITER ;

[wcj1981]

多谢楼上的，有点复杂，我慢慢消化！