MySQL使用auto_increment id和select query创建临时表

want to create a temporary table that has an auto_increment field plus a field that has to be select from another table.

想要创建一个临时表,该表具有auto_increment字段以及必须从另一个表中选择的字段。

Here is what I have (does not work)

这是我的(不起作用)

CREATE TEMPORARY TABLE tmp  (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
(SELECT valueName AS valueName
FROM sometable
WHERE sometable.somevalue='00'));

these work by them selves but can get the right syntax to do both

这些工作由他们自己完成,但可以得到正确的语法来做到这两点

CREATE TEMPORARY TABLE tmp  (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)

CREATE TEMPORARY TABLE tmp AS SELECT valueName AS valueName FROM sometable
WHERE sometable.somevalue='00';

2 个解决方案

#1

CREATE TEMPORARY TABLE tmp (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    valueName  varchar(16) // whatever type it should be
);

INSERT INTO tmp (valueName) SELECT valueName FROM sometable WHERE ...

Relevant docs here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

相关文档:http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

#2

I think you might be trying to do the first case outlined here:

我想你可能会尝试做这里概述的第一个案例:

http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html

..which for your example would look like:

..你的例子看起来像:

CREATE TEMPORARY TABLE tmp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) 
SELECT valueName AS valueName FROM sometable
WHERE sometable.somevalue='00';

..so it might just be the parens in the wrong places that bit you in your first try.

..所以它可能只是在你第一次尝试咬你的错误地方的parens。

#1

CREATE TEMPORARY TABLE tmp (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    valueName  varchar(16) // whatever type it should be
);

INSERT INTO tmp (valueName) SELECT valueName FROM sometable WHERE ...