当前位置：首页 > news >正文

MySQL表达式之公用表表达式(CTE)的使用示例

news 2025/5/19 0:37:15

示例一

数据表中有每个企业每年每月并且每月的产值是累加的数据的数据记录

需求：

统计企业产值能力，找出所有家企业中产值最高的企业，其产值记为P。对于第i家企业，其产值为Pi则该企业的产值能力评分=Pi/P×100。

SQL：

--        使用ROW_NUMBER()为每个企业每年每个月的产值排名，筛选出每个企业每年最大月份的产值。WITH MaxMonthlyOutput AS (SELECT company_id,declare_year,declare_month,total_industrial_value,ROW_NUMBER() OVER (PARTITION BY company_id, declare_year ORDER BY declare_month DESC) as rnFROM company_revenue_info),
--             从MaxMonthlyOutput中筛选出每个企业每年最大月份的产值。MaxYearlyOutput AS (SELECT company_id,declare_year,total_industrial_valueFROM MaxMonthlyOutputWHERE rn = 1),
--             计算每个企业每年的最大月份产值的总和，得到Pi。SumYearlyOutput AS (SELECT company_id,SUM(total_industrial_value) as PiFROM MaxYearlyOutputGROUP BY company_id),
--              找出所有企业的Pi中的最大值，记为P。MaxPi AS (SELECT MAX(Pi) as PFROM SumYearlyOutput)
--            计算每个企业的得分，公式为Pi/P*100。SELECT syo.company_id         AS companyId,syo.Pi                 AS perValue,mpa.P                  AS standardValue,(syo.Pi / mpa.P * 100) AS scoreFROM SumYearlyOutput syoCROSS JOINMaxPi mpa;

MySQL5兼容语法

SELECT s.company_id AS companyId,COALESCE(s.Pi, 0) AS perValue,  -- 空值转0COALESCE(m.P, 1) AS standardValue,  -- 防止除零错误ROUND((COALESCE(s.Pi, 0) / NULLIF(COALESCE(m.P, 1), 0) * 100), 4) AS score
FROM (SELECT company_id,COALESCE(SUM(total_industrial_value), 0) AS Pi  -- 子查询空值处理FROM (SELECT r.company_id, r.total_industrial_valueFROM company_revenue_info rINNER JOIN (SELECT company_id, declare_year, MAX(declare_month) AS max_monthFROM company_revenue_infoGROUP BY company_id, declare_year) mON r.company_id = m.company_id AND r.declare_year = m.declare_year AND r.declare_month = m.max_month) subGROUP BY company_id) s
CROSS JOIN (SELECT COALESCE(MAX(Pi), 1) AS P  -- 确保标准值不为NULLFROM (SELECT COALESCE(SUM(total_industrial_value), 0) AS PiFROM (SELECT r.company_id, r.total_industrial_valueFROM company_revenue_info rINNER JOIN (SELECT company_id, declare_year, MAX(declare_month)