1.WITH递归公用表达式

  • 表结构

    1. CREATE TABLE `tb_city` (
    2. `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    3. `parent_id` bigint DEFAULT NULL,
    4. `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    5. PRIMARY KEY (`id`)
    6. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

  • 数据源

    1. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (1, NULL, '第一级');
    2. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (2, 1, '第二级');
    3. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (3, 2, '第三级');
    4. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (4, 3, '第四级');
    5. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (5, 1, '第二级副本');
    6. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (6, 2, '第三级副本');
    7. INSERT INTO `mysql_8_0`.`tb_city`(`id`, `parent_id`, `name`) VALUES (7, 3, '第四级副本');

  • 执行递归sql ```sql WITH RECURSIVE temp_tb_city AS ( 

    1.   select tc.id, tc.parent_id, tc.`name` from tb_city tc 
    2.   UNION ALL
    3.   select tc.id,tc.id,CONCAT(ttc.`name`,'>',tc.`name`) from tb_city tc,temp_tb_city ttc WHERE ttc.id=tc.parent_id

    ) select * from temp_tb_city

  1. <a name="lagui"></a>
  2. ## 2.
  3. ```sql
  4. -- 按照部门统计以入司时间升序分析与两个相邻两名员工的平均工资
  6. -- 统计每个部门的平均工资
  7. -- select dname,avg(sal) FROM employee group by dname;
  9. -- 让每一位员工等于该岗位的平均工资
  11. -- select *,avg(sal) over(partition by job) from employee ;
  13. -- 按照入职时间降序统计每个部门员工的工资累加值
  14. -- select * ,sum(sal) over(PARTITION by dname order by hiredate desc)from employee;
  16. -- 按照 入职时间升序统计相邻两名员工的平均工资
  17. select * ,sum(sal) over(PARTITION by dname order by hiredate asc rows BETWEEN 1 preceding and 0 following )from employee;