mysql - 从现有表语句调试 MySQL CREATE TABLE？

Question

为什么这会给我一个错误？

我还没有在网上找到任何将 CREATE TABLE 与带有 WHERE 的 SELECT 语句组合在一起的好的示例代码。

CREATE TABLE tmp_year (source CHAR(3),
                       target CHAR(3),
                       val FLOAT,
                       PRIMARY KEY (source, target))
(SELECT source,
        target,
        val
 WHERE date>='2001-01-01'
 AND date<='2001-12-12')
FROM db;

错误信息:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE date>='2001-01-01' AND date<='2001-12-12') FROM db' at line 1

Answer 1

试试这个我的 friend :

CREATE TABLE tmp_year AS
SELECT * FROM YOURTABLE
WHERE date>='2001-01-01'
AND date<='2001-12-12';

ALTER TABLE tmp_year ADD PRIMARY KEY(source, target);

这是 SQL Fiddle 中的示例