mysql 业务开发常用sql 语句总结

分组聚合 GROUP_CONCAT()

使用总结

SELECT emp_id, first_name, last_name, dep_id, 
GROUP_CONCAT( quality ) as "qualities" 
FROM Employee group by emp_id;

参考连接
1 https://www.educative.io/edpresso/what-is-the-groupconcat-function-in-mysql
2 https://stackoverflow.com/questions/14096153/group-concat-not-working-for-some-reason

跨表更新 update inner join

update test a 
inner JOIN  app on app.id = a.app_id
set a.app_name = app.app_name
where 1=1

mysql 5.X 版本实现递归查询

|------|-------------------|-------------|
|  id  |       title       |  parent_id  |
|------|-------------------|-------------|
|  1   |    Dashboard      |      0      |
|  2   |    Content        |      0      |
|  3   |    Modules        |      0      |
|  17  |    User Modules   |      3      |
|  31  |    Categories     |      17     |
|  ... |                   |             |
|------|-------------------|-------------|

SELECT parentsTable._id, GROUP_CONCAT(parentsTable.parent_id SEPARATOR ',') as concatenatedParents FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 31, @l := 0) vars,
        menu m
    WHERE @r <> 0
) as parentsTable

执行结果

|------|----------------------|
|  id  | concatenatedParents  |
|------|----------------------|
|  1   |  0                   |
|  2   |  0                   |
|  3   |  0                   |
|  17  |  3,0                 |
|  31  |  17,3,0              |
|  ... |                      |
|------|----------------------|