mysql - 创建表作为 select from 子句

Question

我有表 T1，它有 3 个文本列:a、b、c。现在我想使用以下查询从 T1 创建表 T2

create table T2 as 
   select
    a,b, sum(c) as sum_col 
   from T1 
    where 'some where condition here'

现在 sum_col 列是用 double 数据类型创建的，我希望它被创建为 decimal(20,7)。有人可以建议有什么办法吗？

Answer 1

您可以使用cast 函数为派生列定义新的数据类型。

create table T2 as
 select a,b, cast( sum(c) as decimal(20,7) ) as sum_col
 from T1
  where 'some condition here'

MySQL fiddle :Demo

---

MySQL 命令提示符下的内联演示:

mysql> create table t1( i int );
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> insert into t1 values( 6 ), ( 9 );
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table t2 as
    ->  select cast( sum(i) as decimal(20,7) ) as sum_total
    ->  from t1;
Query OK, 1 row affected (0.45 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| sum_total | decimal(20,7) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> select * from t2;
+------------+
| sum_total  |
+------------+
| 15.0000000 |
+------------+
1 row in set (0.00 sec)