mysql实现查询数据并根据条件更新到另一张表的方法示例

select * from goods;
+------+------+
| id| price|
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

select id, group_concat(price) from goods group by id;
+------+--------------------+
| id| group_concat(price) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

select id,group_concat(distinct price) from goods group by id;
+------+-----------------------------+
| id| group_concat(distinct price) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

select id,group_concat(price order by price desc) from goods group by id;
+------+---------------------------------------+
| id| group_concat(price order by price desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name

select * from insert_one;
+----+--------+-----+-----+
| id | name  | age | sex |
+----+--------+-----+-----+
| 1 | 田小斯 | 25 |   |
| 2 | 刘大牛 | 26 |   |
| 3 | 郑大锤 | 28 |   |
| 4 | 胡二狗 | 30 |   |
+----+--------+-----+-----+
4 rows in set

 
select * from insert_sex;
+----+-----+
| id | sex |
+----+-----+
| 1 | 1  |
| 2 | 2  |
| 3 | 1  |
| 4 | 2  |
+----+-----+
4 rows in set

into insert_one(sex) select sex from insert_sex;
Query OK, 4 rows affected
select * from insert_one;
+----+--------+-----+-----+
| id | name  | age | sex |
+----+--------+-----+-----+
| 1 | 田小斯 | 25 |   |
| 2 | 刘大牛 | 26 |   |
| 3 | 郑大锤 | 28 |   |
| 4 | 胡二狗 | 30 |   |
| 5 |    |   | 1  |
| 6 |    |   | 2  |
| 7 |    |   | 1  |
| 8 |    |   | 2  |
+----+--------+-----+-----+
8 rows in set

update insert_one,insert_sex set insert_one.sex = insert_sex.sex where insert_one.id = insert_sex.id;
Query OK, 4 rows affected
select * from insert_one;
+----+--------+-----+-----+
| id | name  | age | sex |
+----+--------+-----+-----+
| 1 | 田小斯 | 25 | 1  |
| 2 | 刘大牛 | 26 | 2  |
| 3 | 郑大锤 | 28 | 1  |
| 4 | 胡二狗 | 30 | 2  |
| 5 |    |   | 1  |
| 6 |    |   | 2  |
| 7 |    |   | 1  |
| 8 |    |   | 2  |
+----+--------+-----+-----+
8 rows in set