MySql row_number TOP5 排队编号_mysql5排序编号-CSDN博客

本文链接：https://blog.csdn.net/concealed0/article/details/108241344

报表统计，要求展示会员第一次购买、第二次购买、第三次购买金额与流水号。

如果不是使用MySQL，直接使用row_number函数对一个会员购买次数进行排序就可以了，但是目前MySQL5.16不支持row_number函数。

所以查询了下资料，使用MySQL简单进行流式处理。

SELECT
  IF
	(@num = sale.vip_code, @rank := @rank + 1, @rank := 1 ) AS rank,
	@num := sale.vip_code  vip_code ,----- 会员编码,
	sale.vip_code,     ----- 会员编码
	sale.crt_date,     ----- 交易时间
	sale.sale_no,      ----- 流水号
	sale.fin_total     ----- 金额
FROM
	vip_con_sale sale     -----交易流水表
WHERE
	sale.crt_date >= date_sub( now( ), INTERVAL 100 DAY )  ----- 查询最近100天数据
ORDER BY
	sale.vip_code ASC,   ----- 按会员号排序
	sale.crt_date ASC    ----- 根据时间进行排序

结果如下图：

然后进行行转列操作，即对会员分组后，通过case when then else end 进行判断。

SELECT
	base.vip_code,
	sum( CASE WHEN base.rank = 1 THEN fin_total ELSE 0 END ) AS oneBuy,
	sum( CASE WHEN base.rank = 1 THEN sale_no ELSE 0 END ) AS oneBuySaleNo,
	sum( CASE WHEN base.rank = 2 THEN fin_total ELSE 0 END ) AS twoBuy,
	sum( CASE WHEN base.rank = 2 THEN sale_no ELSE 0 END ) AS twoBuySaleNo,
	sum( CASE WHEN base.rank = 3 THEN fin_total ELSE 0 END ) AS thrBuy,
	sum( CASE WHEN base.rank = 3 THEN sale_no ELSE 0 END ) AS thrBuySaleNo 
FROM
	(
SELECT
IF
	( @num = sale.vip_code, @rank := @rank + 1, @rank := 1 ) AS rank,
	@num := sale.vip_code vip_code,
	sale.crt_date,
	sale.sale_no,
	sale.fin_total 
FROM
	vip_con_sale sale 
WHERE
	sale.crt_date >= date_sub( now( ), INTERVAL 100 DAY ) 
ORDER BY
	sale.vip_code ASC,
	sale.crt_date ASC 
	) base
GROUP BY
	base.vip_code