示例sql,此sql不能应用于生产,生成rowNumber可以参考。主要是没对country、region等进行group。
select * from
(select
-- 定义变量 rn, year, month,变量以@开头。满足条件将变量rn+1
@rn:= case when @year=b.year and @month = b.month then @rn+1 else 1 end as rn, b.sales_volume, b.country,b.region,
-- 将变量@year等赋值
b.brand,b.kd_cbu,b.prod_mode, @year:=b.year as year, @month:=b.month as month from
(select *
from
(SELECT
case when country is null or country = '#N/A' then '其他' else country end as country,
case when region is null or region = '#N/A' then '其他' else region end as region,
YEAR,month,
sum(sales_volume) as sales_volume,
case when brand is null then '其他' else brand end as brand,
kd_cbu,
prod_mode
FROM
omda_data_analysis
where year = 2021
GROUP BY year, month,
brand) as s_1 ORDER BY s_1.year,s_1.month, s_1.sales_volume desc) b
) a
mysql5.7实现row_number() over()
于 2022-03-24 20:04:34 首次发布
本文介绍了一个示例SQL查询,该查询展示了如何在不进行GROUP BY操作的情况下生成行号,并对特定字段进行分组。查询中定义了变量并根据年份和月份对行号进行递增,同时对缺失或异常值进行了处理,例如将NULL或'#N/A'替换为'其他'。整个查询适用于2021年的数据,按销量降序排列。
摘要由CSDN通过智能技术生成