背景:产品升级需要兼容MySQL环境运行,需要将原有的SQL server语句转成MySQL写法。
SQL server:
分组排序语法: Row_Number() over(partition by 分组字段A,分组字段B,... Order By 排序字段...)
然而MySQL中没有该语法,可以使用参数的方式进行实现。
查询模板:
1、partition by后跟一个参数
SQL server:
SELECT Row_Number() over(partition by A.字段2 Order By 排序字段)
FROM 表A
MySQL:
SELECT IF(@P = A.字段2,@R:=@R + 1,@R:=1) AS 分组排序字段
,@P := A.字段2 AS 给参数赋值1
FROM (SELECT @R:=0) AS Q,(
SELECT 字段1,字段2,字段3
FROM AS 表A,(SELECT @P:=NULL) AS B
) AS A
ORDER BY 排序字段
2、partition by后跟两个参数
SQL server:
SELECT Row_Number() over(partition by A.字段2,A.字段3 Order By 排序字段)
FROM 表A
MySQL:
SELECT IF(@P = A.字段2 AND @G = A.字段3,@R:=@R + 1,@R:=1) AS 分组排序字段
,@P := A.字段2 AS 给参数赋值1, @G := A.字段3 AS 给参数赋值2
FROM (SELECT @R:=0) AS Q,(
SELECT 字段1&#x