这样不是我想要的结果 括号中( select * from A where .....)T 这个查询返回一个视图,再用select * from 返回的还是这个视图。我不是我想要的结果! 我想的结果是 select tablename from A where .. 返回一堆表名,我然后再用select * from [?,?,...]查结果,其中?号部分就是第一个表中查出的tablename结果
貌似不行, 我尝试 select * from case 1 when 1 then 'test1' when 2 then 'test2' end; 不成功
select ’select * from '||table_name||‘;’ as s from yourtablename; 执行一下能够得到你要查询的表的查询sql,然后执行这些sql 可以得到n个结果集
MYSQL中不用存储过程,则无解。
CREATE TABLE t_test_timestamp (id INT, TIME TIMESTAMP, NAME VARCHAR(10));
INSERT INTO t_test_timestamp VALUES (1,CURRENT_TIMESTAMP,'aa'), (2,CURRENT_TIMESTAMP+INTERVAL 1 SECOND,'bb'), (3,CURRENT_TIMESTAMP+INTERVAL 3 SECOND,'cc');CREATE TABLE t_test_dynamic (id INT, NAME VARCHAR(50));
INSERT INTO t_test_dynamic VALUES(1,'t_test_timestamp');
SET @sql_text:='select name into @s1 from t_test_dynamic where id=1'; PREPARE stmt FROM @sql_text; EXECUTE stmt; SELECT @s1; SET @sql_text:=CONCAT('select count(*) into @s2 from ',@s1); PREPARE stmt FROM @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT @s2;
我想的结果是
select tablename from A where .. 返回一堆表名,我然后再用select * from [?,?,...]查结果,其中?号部分就是第一个表中查出的tablename结果
我尝试 select * from case 1 when 1 then 'test1' when 2 then 'test2' end;
不成功
(id INT,
TIME TIMESTAMP,
NAME VARCHAR(10));
INSERT INTO t_test_timestamp
VALUES
(1,CURRENT_TIMESTAMP,'aa'),
(2,CURRENT_TIMESTAMP+INTERVAL 1 SECOND,'bb'),
(3,CURRENT_TIMESTAMP+INTERVAL 3 SECOND,'cc');CREATE TABLE t_test_dynamic
(id INT,
NAME VARCHAR(50));
INSERT INTO t_test_dynamic VALUES(1,'t_test_timestamp');
SET @sql_text:='select name into @s1 from t_test_dynamic where id=1';
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
SELECT @s1;
SET @sql_text:=CONCAT('select count(*) into @s2 from ',@s1);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @s2;