根据产品品类编号分组后按照产品安全指数排序,并加序号。
SELECT
id,
product_classify_id,
p_coefficient,
rank
FROM
(
SELECT
*,@rownum :=@rownum + 1 AS rownum,
IF (
@pa = ff.product_classify_id ,@rank :=@rank + 1 ,@rank := 1
) AS rank ,@pa := ff.product_classify_id
FROM
(
SELECT
id,
product_classify_id,
p_coefficient
FROM
jp_product
WHERE
enabled = 1
GROUP BY
product_classify_id,
id
ORDER BY
CAST(
product_classify_id AS signed
) ASC,
CAST(
p_coefficient AS DECIMAL (3, 2)
) DESC
) ff,
(
SELECT
@rank := 0 ,@rownum := 0 ,@pa = NULL
) tt
) result
id:产品编号,product_classify_id:产品品类编号,p_coefficient:产品安全指数,rank:序号。
CAST用于将字符串转为数字。
查询结果如下: