SQL。加盟销售排名

Question

我有这样的表：、orders、和products。如何在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);

​

​

有效结果是(伯爵->级)：

• 2 -> 3
• 2 -> 3
• 4个-> 2
• 4个-> 2
• 6个-> 1

Answer 1

这个查询应该能做到这一点。

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仍然保存上一行的值。

最后，您必须将分组查询放在子查询中。人们不会认为这是必要的，因为查询的逻辑处理方式如下

1. 从…
2. 哪里
3. 按组
4. 有
5. 命令由
6. 选择

但是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)

Answer 2

您可以使用具有相同结果集的内部联接来完成该操作。

mysql> select DONATUR,COUNT(DISTINCT AREA) ,@curRank := @curRank + 1 AS rank from funding,(SELECT @curRank := 0) r group by Donatur;               +---------+----------------------+------+
| DONATUR | COUNT(DISTINCT AREA) | rank |
+---------+----------------------+------+
| Mr.X    |                    3 |    1 |
| Mr.Y    |                    1 |    2 |
| Mr.Z    |                    2 |    3 |
| sss     |                    0 |    4 |
| wwww    |                    0 |    5 |
+---------+----------------------+------+
5 rows in set (0.00 sec)

 SELECT x.DONATUR,x.area,if(x.rank>y.rank,y.rank,x.rank) AS rank FROM (select DONATUR,COUNT(DISTINCT AREA) as area ,@curRank := @curRank + 1 AS rank from funding,(SELECT @curRank := 0) r group by Donatur) x LEFT JOIN  (select DONATUR,COUNT(DISTINCT AREA) as area ,@curRank2 := @curRank2 + 1 AS rank from funding,(SELECT @curRank2 := 0) r group by Donatur) y on y.rank<>x.rank and x.area=y.area;
+---------+------+------+
| DONATUR | area | rank |
+---------+------+------+
| Mr.X    |    3 |    1 |
| Mr.Y    |    1 |    2 |
| Mr.Z    |    2 |    3 |
| sss     |    0 |    4 |
| wwww    |    0 |    4 |
+---------+------+------+
5 rows in set (0.00 sec)