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; 请问要是实现这样的查询,语句应该怎么写?我想我这次是说明白了。
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>这就是你的测试数据?!
Try:SET @sql=(SELECT en FROM a WHERE a.en='stuff'); SET @sql=CONCAT('SELECT ',@sql,' FROM b'); PREPARE stmt FROM @sql; EXECUTE stmt;
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]
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]
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');
to mysqi:执行你写的语句后,出现如下这种错误。 Unknown prepared statement handler (stmt) given to EXECUTE
要用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 ;
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
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;
请问要是实现这样的查询,语句应该怎么写?我想我这次是说明白了。
-> 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>这就是你的测试数据?!
SET @sql=CONCAT('SELECT ',@sql,' FROM b');
PREPARE stmt FROM @sql;
EXECUTE stmt;
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]
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]
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');
Unknown prepared statement handler (stmt) given to EXECUTE
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 ;