MySQL如何将select子查询结果横向拼接后插入数据表中

我的要求具体是这样的。我有数据表audit的结构如下：

+-----------+------------+------+-----+-------------------+-------+
| Field     | Type       | Null | Key | Default           | Extra |
+-----------+------------+------+-----+-------------------+-------+
| auditDate | date       | NO   |     | NULL              |       |
| invNum    | int(11)    | NO   |     | NULL              |       |
| fltNum    | int(11)    | NO   |     | NULL              |       |
| auditNum  | int(11)    | NO   |     | NULL              |       |
| missNum   | int(11)    | NO   |     | NULL              |       |
| invType   | tinyint(4) | NO   |     | NULL              |       |
| time      | timestamp  | NO   |     | CURRENT_TIMESTAMP |       |
+-----------+------------+------+-----+-------------------+-------+

除了最后一列默认是当前时间戳，每一列的结果都是一个select查询结果。如何将查询的结果合并成一条记录插入到上面的数据表中呢？网上也没有确切的答案，摸索了很久，最后，终于在百般尝试下使用join进行横向拼接完成了我想要的功能！参考sql语句如下：

insert into audit_template(auditDate,invNum,fltNum,auditNum,missNum,invType) select * from (select current_date)tmp1 join (select 1)tmp2 join (select 1 as fltNum)tmp3 join (select 6 as auditNum)tmp4 join (select 2)tmp5 join (select 1)tmp6;

插入成功后，查询结果如下：

select * from audit;
+------------+--------+--------+----------+---------+---------+---------------------+
| auditDate  | invNum | fltNum | auditNum | missNum | invType | time                |
+------------+--------+--------+----------+---------+---------+---------------------+
| 2016-07-02 |      1 |      1 |        6 |       2 |       1 | 2016-07-02 11:13:39 |
+------------+--------+--------+----------+---------+---------+---------------------+

拓展一下，如果我现在想让audit表中的missNum的值是auditNum>fltNum?auditNum-fltNum:0，那么该如何做呢？自己又摸索了一下，参考如下sql，在一条语句中完成，当然你也可以再插入后对数据表进行update。

insert into audit(auditDate,invNum,fltNum,auditNum,missNum,invType) select * from (select current_date)tmp1 join (select 1)tmp2 join (select 12)tmpFlt join (select 6)tmpAudit join (select if(tmpAudit.audit-tmpFlt.flt>0,tmpAudit.audit-tmpFlt.flt,0) as miss from (select 12 as flt)tmpFlt,(select 6 as audit)tmpAudit)tmp5 join (select 1)tmp6;

上面的语句和前面基本相同，只是增加了对tmpFlt和tmpAudit这两个子查询的重复查询，降低了时间效率。

 

原文地址：https://cloud.tencent.com/developer/article/1176694