在mysql中如何将一个函数的执行结果做为一个表名来查询？？

lijpwsw 2011-07-11 11:00:56

select * from concat("test_",DATE_FORMAT(now(),'%Y%m')) ;

concat("test_",DATE_FORMAT(now(),'%Y%m')) 得到的结果是 test_201107

我想查询test_201107这个表中的数据

如何做

select * from concat("test_",DATE_FORMAT(now(),'%Y%m')) ;这句话是报错的

求高手指点

...全文

1578 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

lijpwsw 2011-07-11

打赏
举报

英文超级烂看不懂英文文档啊^

lijpwsw 2011-07-11

打赏
举报

[Quote=引用 6 楼 shine333 的回复:]

你“单独拿出来”执行过一遍了？会不会用“PREPARE STML FROM @ASQL”的时候，导致主键冲突？
[/Quote]

在执行的时候我已经清空原来的表了

shine333 2011-07-11

打赏
举报

你“单独拿出来”执行过一遍了？会不会用“PREPARE STML FROM @ASQL”的时候，导致主键冲突？

lijpwsw 2011-07-11

打赏
举报

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

呵呵，动态SQL语句
mysql help
PREPARE stmt_name FROM preparable_stmt

The PREPARE statement prepares a statement and assigns it a name, stmt_name, by which to refer to the statement later. Statement nam……
[/Quote]

我执行的时候有些问题

生成的sql语句是这样的

insert into test select * from test_201107;

单独拿出来就可以
但是用PREPARE STML FROM @ASQL;
就不行

wwwwb 2011-07-11

打赏
举报

呵呵，动态SQL语句
mysql help
PREPARE stmt_name FROM preparable_stmt

The PREPARE statement prepares a statement and assigns it a name, stmt_name, by which to refer to the statement later. Statement names are not case sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the statement. The text must represent a single SQL statement, not multiple statements. Within the statement, ‘?’ characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The ‘?’ characters should not be enclosed within quotes, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists.

The scope of a prepared statement is the client session within which it is created. Other clients cannot see it.

EXECUTE stmt_name [USING @var_name [, @var_name] ...]

After preparing a statement, you execute it with an EXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter markers, you must supply a USING clause that lists user variables containing the values to be bound to the parameters. Parameter values can be supplied only by user variables, and the USING clause must name exactly as many variables as the number of parameter markers in the statement.

You can execute a given prepared statement multiple times, passing different variables to it or setting the variables to different values before each execution.

lijpwsw 2011-07-11

打赏
举报

[Quote=引用 2 楼 wwwwb 的回复:]

SET @ASQL=CONCAT('select * from test_',DATE_FORMAT(now(),'%Y%m')) ;
PREPARE STML FROM @ASQL;
EXECUTE STML;
[/Quote]

能把每一句话都解释一下不

我试了可以但是不知道是什么意思……

wwwwb 2011-07-11

打赏
举报

SET @ASQL=CONCAT('select * from test_',DATE_FORMAT(now(),'%Y%m')) ;
PREPARE STML FROM @ASQL;
EXECUTE STML;

iihero 2011-07-11

打赏
举报

mysql> select concat("test_",DATE_FORMAT(now(),'%Y%m'));

+-------------------------------------------+

| concat("test_",DATE_FORMAT(now(),'%Y%m')) |

+-------------------------------------------+

| test_201107                               |

+-------------------------------------------+

1 row in set (0.03 sec)

昆泽 2011-07-11

打赏
举报

留名标记

lijpwsw 2011-07-11

打赏
举报

两个帖子的分都是你的……

lijpwsw 2011-07-11

打赏
举报

[Quote=引用 17 楼 wwwwb 的回复:]

SET @ASQL = CONCAT("INSERT INTO tmp( user_id, num ,`type`,`from`)
SELECT user_id, count(*) AS num ,method,`",CONCAT('lomo_track_',
DATE_FORMAT(NOW(),'%Y%m')),'` from track_',DATE_FORMAT(NOW(),'%……
[/Quote]

太谢谢你了每次我都没有执行execute stml;

所以得不到结果

wwwwb 2011-07-11

打赏
举报

SET @ASQL = CONCAT("INSERT INTO tmp( user_id, num ,`type`,`from`)
SELECT user_id, count(*) AS num ,method,`",CONCAT('lomo_track_',
DATE_FORMAT(NOW(),'%Y%m')),'` from track_',DATE_FORMAT(NOW(),'%Y%m'),
" WHERE method in ('photo','comment','likes','followers','followings') and user_id > 0 GROUP BY user_id
");
PREPARE STML FROM @ASQL;
execute stml;

lijpwsw 2011-07-11

打赏
举报

mysql> PREPARE STML FROM @ASQL;
Query OK, 0 rows affected (0.01 sec)
Statement prepared

lijpwsw 2011-07-11

打赏
举报

[Quote=引用 13 楼 wwwwb 的回复:]

SET @ASQL = CONCAT("INSERT INTO tmp( user_id, num ,`type`,`from`)
SELECT user_id, count(*) AS num ,method,`",CONCAT('lomo_track_',
DATE_FORMAT(NOW(),'%Y%m')),'` from track_',DATE_FORMAT(NOW(),'%……
[/Quote]

不行啊

lijpwsw 2011-07-11

打赏
举报

wwwwb 2011-07-11

打赏
举报

lijpwsw 2011-07-11

打赏
举报

[Quote=引用 11 楼 wwwwb 的回复:]

贴建表及插入记录的SQL
[/Quote]

set @ASQL = CONCAT("INSERT INTO tmp( user_id, num ,`type`,`from`)
SELECT user_id, count( * ) AS num ,method,'",concat('lomo_track_',
DATE_FORMAT(now(),'%Y%m')) ,
"' from track_",
DATE_FORMAT(now(),'%Y%m'),
" WHERE method in ('photo','comment','likes','followers','followings') and user_id > 0 GROUP BY user_id
");

PREPARE STML FROM @ASQL;

wwwwb 2011-07-11

打赏
举报

贴建表及插入记录的SQL

lijpwsw 2011-07-11

打赏
举报

[Quote=引用 9 楼 wwwwb 的回复:]

你的代码是什么
[/Quote]

我的代码就是想执行 insert into test select * from test_201107;

但是我去PREPARE STML FROM @ASQL;执行是不成功的我select @ASQL看到那个sql语句拿出来执行是可以的

是不是不能用insert啊

wwwwb 2011-07-11