INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '漏扫专员', 0, NULL, 0); INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '成员', 0, NULL, 0); INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '单位接口人', 0, NULL, 0); 用sql实现上面三条语句:合并成这条语句 INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time") VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '漏扫专员,成员,单位接口人', 0, NULL, 0);
时间: 2023-08-23 10:05:25 浏览: 126
好的,你的问题是如何用 SQL 实现将上面三条语句合并成一条语句?
可以使用 INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE ... 语句来实现。具体的语句如下:
INSERT INTO ""("id", "user_name", "real_name", "email", "org_id", "last_login_time", "created_time", "valid_period", "always", "name", "lock_status", "latest_failed_time", "wrong_pwd_time")
VALUES (575526910805672420, '13330291922', '袁和姣', 'nancy.yy@foxmail.com', 454544777304604693, '2023-05-17 15:25:19', '2023-05-08 11:38:28.304166', '2023-05-08 11:38:28.304166', 't', '漏扫专员,成员,单位接口人', 0, NULL, 0)
ON DUPLICATE KEY UPDATE name = CONCAT(name, ',漏扫专员,成员,单位接口人');
其中,VALUES 子句中的数据是要插入的数据,ON DUPLICATE KEY UPDATE 子句中的语句是在插入数据时,如果存在相同的主键,则更新指定的字段(这里是 name 字段),使用 CONCAT 函数将原来的值和新值合并。