LEN

Mysql INSERT INTO SELECT 批量插入SQL 实例
Mysql sql 需求将统计后数据覆盖如一张表中, 批量操作有记录则update 无记录则插入;下面这条sql ...
扫描右侧二维码阅读全文
11
2017/06

Mysql INSERT INTO SELECT 批量插入SQL 实例

Mysql sql 需求将统计后数据覆盖如一张表中, 批量操作有记录则update 无记录则插入;

下面这条sql 满足上述需求:

INSERT INTO mytable (id, `name`, sum) 
SELECT class, max_age, num
FROM
    (
        SELECT
            class,
            max(age) max_age,
            count(1) num
        FROM
            tb_baby
        GROUP BY
            class
    ) tb 
ON DUPLICATE KEY UPDATE 
    mytable.id = tb.class,
    mytable.`name` = tb.max_age,
    mytable.sum = tb.num

过往写这类数据统计脚本基本都是读出统计后逐条update
使用insert into select 就省了大事了, 所以说SQL 还是需要多学多用, 最近找mysql书啃一啃吧

Last modification:June 11th, 2017 at 10:58 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment