#公共表达式 WITH临时表

最新推荐文章于 2024-03-20 20:43:53 发布

m0_38045653

最新推荐文章于 2024-03-20 20:43:53 发布

阅读量167

点赞数

分类专栏： mysql 文章标签： mysql sql

本文链接：https://blog.csdn.net/m0_38045653/article/details/105534108

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏




  SELECT 
         Q1.YEAR LAST_YEAR
        ,Q1.SUM AS LAST_YEAR_SUM
        ,Q2.YEAR THIS_YEAR
        ,Q2.SUM AS THIS_YEAR_SUM
        ,CONCAT(ROUND((Q2.SUM - Q1.SUM)/(CASE Q1.SUM WHEN 0 THEN NULL ELSE Q1.SUM END ),2) * 100,'%') AS GROW_RATE 
      FROM (SELECT YEAR(FROM_DATE) AS YEAR,SUM(SALARY) AS SUM FROM SALARIES GROUP BY YEAR) AS Q1
          ,(SELECT YEAR(FROM_DATE) AS YEAR,SUM(SALARY) AS SUM FROM SALARIES GROUP BY YEAR) AS Q2
      WHERE Q1.YEAR = Q2.YEAR-1
      
      
      
 # 常规CTE
 
 WITH SUM AS (SELECT YEAR(FROM_DATE) AS YEAR
                     ,SUM(SALARY) AS SUM 
                    FROM SALARIES
                   GROUP BY YEAR
)
 SELECT  Q1.YEAR LAST_YEAR
        ,Q1.SUM AS LAST_YEAR_SUM
        ,Q2.YEAR THIS_YEAR
        ,Q2.SUM AS THIS_YEAR_SUM
        ,CONCAT(ROUND((Q2.SUM - Q1.SUM)/(CASE Q1.SUM WHEN 0 THEN NULL ELSE Q1.SUM END ),2) * 100,'%') AS GROW_RATE 
      FROM SUM AS Q1,SUM AS Q2
     WHERE Q1.YEAR =Q2.YEAR-1
      
# 递归CTE    学习有点不懂 
 WITH RECURSIVE TMP(N) AS (
   SELECT 1
    UNION ALL 
   SELECT N+1 FROM TMP WHERE N<5
 )
 SELECT * FROM TMP;
 
 
 CREATE TABLE EMPLOYEES_MGR(
  ID INT PRIMARY KEY NOT NULL COMMENT '员工编号'
  ,NAME VARCHAR(100) NOT NULL COMMENT '员工姓名'
  ,MANAGER_ID INT NULL  COMMENT '领导工号'
  ,INDEX (MANAGER_ID)
  ,FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES_MGR(ID)
 );
 INSERT INTO EMPLOYEES_MGR VALUES
  (333,'SUN YANZI',NULL)
  ,(198,'JOHN ZENG',333)
  ,(692,'TAREK',333)
  ,(29,'PEDRO LIU',198)
  ,(4610,'SARAH SZ',29)
  ,(72,'PIERRE',29)
  ,(123,'ADIL',692)

  #注意别写错 RECURSIVE
  WITH RECURSIVE EMPLOYEE_PATHS (ID,NAME,PATH) AS ( 
   SELECT ID 
          ,NAME
          ,CAST(ID AS CHAR(200)) 
        FROM EMPLOYEES_MGR 
       WHERE MANAGER_ID IS  NULL
   UNION ALL
   SELECT 
         G.ID
         ,G.NAME
         ,CONCAT(P.path,',',G.ID)
       FROM EMPLOYEES_MGR G,EMPLOYEE_PATHS P
      WHERE G.MANAGER_ID = P.ID
   
 )
 
 SELECT * FROM EMPLOYEE_PATHS;
 
 
  WITH RECURSIVE employee_paths (id , NAME , path) AS (
	SELECT id
	      ,NAME
	      ,CAST(id AS CHAR(200)) AS path
	 FROM employees_mgr
	WHERE manager_id IS NULL
		UNION ALL
	SELECT e.id
	      , e.name 
	      , CONCAT(ep.path,',',e.id)
	FROM employee_paths AS ep JOIN employees_mgr AS e
	ON ep.id = e.manager_id
	
	)
SELECT * FROM employee_paths ORDER BY path ;
  
#表结构查询
 SELECT c.column_name
        ,c.data_type
        ,c.column_type
        ,c.column_comment 
        ,t.table_name
        ,t.table_comment
      FROM INFORMATION_SCHEMA.Columns c,INFORMATION_SCHEMA.tables t
     WHERE c.tABLE_SCHEMA=t.tABLE_SCHEMA
           AND c.table_name =t.table_name
           AND t.tABLE_SCHEMA='ccs' 
           AND t.TABLE_NAME='approve_flow_info' 
    ORDER BY c.ordinal_position ASC