MySQL查询到动态“排名行" [英] MySQL query to dynamic "Ranking rows"

问题描述

我在运行查询排名时遇到问题. 内部SELECT按行顺序给出行，对于每行，变量@rank如果位置不等于先前的行，则会增加. 但是@rank并不是正确的位置.

I'm having problems running a query ranking. The inner SELECT gives the rows in order of ranking, for each line, the variable @rank increases, if not a position equal to the previous ranking. But the @rank is not really the correct position.

我正在尝试按价值最高的人群进行分组和排序.

I'm trying to do a ranking grouped and ordered by those with the highest value.

SET @prev := NULL;
SET @curr := NULL;
SET @rank := 0;
SELECT
    @prev := @curr,
    @curr := SUM( a.value ) AS SUM_VALUES,
    @rank := IF(@prev = @curr, @rank, @rank+1) AS rank,
    b.id AS b_id,
    b.name AS b_nome

FROM
    a INNER JOIN b ON ( a.b_id = b.id )

GROUP BY b.id
ORDER BY SUM_VALUES DESC;

结果:

----------------------------------------------------
@prev := @curr | SUM_VALUES | rank | b_id  | b_nome
---------------|------------|------|-------|--------
NULL           | 10         | 2    | 2     | BBB
NULL           | 2          | 1    | 1     | AAA

在这里，BBB排名第一，AAA排名第二. 但这不会发生，一种关于正在发生的事情的想法?

Here BBB was to return in the first place in the ranking and AAA, second in ranking. But this does not occur, one idea of what is happening?

测试转储

CREATE TABLE `a` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `b_id` INT(10) NULL DEFAULT NULL,
    `value` INT(10) NULL DEFAULT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `b_id` (`b_id`),
    CONSTRAINT `fk_b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `b` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

INSERT INTO `b` (`id`, `name`) VALUES (1, 'AAA');
INSERT INTO `b` (`id`, `name`) VALUES (2, 'BBB');

INSERT INTO `a` (`id`, `b_id`, `value`, `name`) VALUES (1, 1, 2, 'smaller');
INSERT INTO `a` (`id`, `b_id`, `value`, `name`) VALUES (2, 2, 10, 'bigger');

推荐答案

具有
这会很慢，但是having子句会在所有选择，联接，位置和分组依据已完成且已完全解决之后，在 之后运行.
唯一的问题是having不使用索引，而where使用索引.

having
It will be slow, but a having clause will run after all the selects, joins, where and group by's have finished and are fully resolved.
The only problem is that having does not use an index, whilst where does use an index.

SELECT
  ranking stuff
FROM 
  lot of tables
WHERE simple_condition
HAVING filters_that_run_last

使您的加入明确
请注意，您不必混合使用显式联接和隐式联接.
如果您想要交叉连接，则可以使用cross join关键字.

Make your joins explicit
Note that you don't have to mix explicit and implicit joins.
If you want a cross join, you can use the cross join keyword.

    ....
    ) AS Ranking
    CROSS JOIN (SELECT @curr := null, @prev := null, @rank := 0) InitVars
WHERE
  Ranking.regional_id = 1003

这篇关于MySQL查询到动态“排名行"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！