我有桌子:订单和产品。如何在MySQL 5.6中生成等级?
如果产品值相同,则等级也必须相同。下面我需要按数量排名
SELECT
count(productpk), productpk,
@prev := @curr,
@curr := count(productpk),
@rank := IF(@prev = @curr, @rank, @rank+1) AS rank
FROM orders AS om
JOIN products AS p ON om.PK=p.p_order,
(SELECT @curr := null, @prev := null, @rank := 0) sel1
GROUP BY productpk ORDER BY count(productpk);
有效结果为(计数->排名):
这个查询应该可以解决问题。
SELECT
sq.productpk,
sq.cp,
@rank := IF(@prev = sq.cp, @rank, @rank + 1) AS rank,
@prev := sq.cp
FROM
(
SELECT
productpk,
COUNT(productpk) AS cp
FROM orders o
JOIN products p ON o.PK = p.p_order
GROUP BY productpk
) sq
, (SELECT @prev := NULL, @rank := 0) var_init_subquery
ORDER BY sq.cp DESC
SELECT
子句中的顺序很重要。当你第一次做这样的事情
@prev := @curr,
然后像这样
@rank := IF(@prev = @curr,...
这毫无意义,因为@prev
它将始终等于@curr
。@curr
顺便说一句,在这种情况下也是毫无意义的。
您必须@prev
与IF()
函数中的当前行进行比较。之后,将当前行分配给@prev
。当读取下一行时,@prev
仍保留上一行的值。
最后,您必须将分组查询放在子查询中。不会有人认为这是必要的,因为查询在逻辑上像
但是MySQL并不是这样做的,至少在涉及用户定义的变量时不是这样。参见以下简单测试作为证明:
root@localhost:playground > select a, @r:=@r+1 as r from bar, (select @r := 0) sq;
+------+------+
| a | r |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
| 2 | 11 |
| 2 | 12 |
| 2 | 13 |
| 2 | 14 |
| 2 | 15 |
+------+------+
15 rows in set (0.00 sec)
root@localhost:playground > select a, @r:=@r+1 as r from bar, (select @r := 0) sq group by a;
+------+------+
| a | r |
+------+------+
| 1 | 1 |
| 2 | 11 |
+------+------+
2 rows in set (0.00 sec)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句