Несколько рангов в одной таблице

Ответ 1

Это немного сложно. Вы можете использовать переменные, например, в следующем примере:

SELECT    ( 
            CASE cust_type 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := cust_type END
          ) + 1 AS rank,
          cust_type,
          cust_name,
          revenue
FROM      sales,
          (SELECT @curRow := 0, @curType := '') r
ORDER BY  cust_type DESC, revenue DESC;

Часть (SELECT @curRow := 0, @curType := '') r позволяет инициализировать переменную без отдельной команды SET.

Тестовый пример:

CREATE TABLE sales (cust_type varchar(10), cust_name varchar(10), revenue int);

INSERT INTO sales VALUES ('Top', 'A', 10000);
INSERT INTO sales VALUES ('Top', 'B', 9000);
INSERT INTO sales VALUES ('Top', 'C', 8000);
INSERT INTO sales VALUES ('Bottom', 'X', 5000);
INSERT INTO sales VALUES ('Bottom', 'Y', 6000);
INSERT INTO sales VALUES ('Bottom', 'Z', 7000);

Результат:

+------+-----------+-----------+---------+
| rank | cust_type | cust_name | revenue |
+------+-----------+-----------+---------+
|    1 | Top       | A         |   10000 |
|    2 | Top       | B         |    9000 |
|    3 | Top       | C         |    8000 |
|    1 | Bottom    | Z         |    7000 |
|    2 | Bottom    | Y         |    6000 |
|    3 | Bottom    | X         |    5000 |
+------+-----------+-----------+---------+
6 rows in set (0.00 sec)

Другой тестовый пример:

CREATE TABLE sales (cust_type varchar(10), cust_name varchar(10), revenue int);

INSERT INTO sales VALUES ('Type X', 'A', 7000);
INSERT INTO sales VALUES ('Type X', 'B', 8000);
INSERT INTO sales VALUES ('Type Y', 'C', 5000);
INSERT INTO sales VALUES ('Type Y', 'D', 6000);
INSERT INTO sales VALUES ('Type Y', 'E', 4000);
INSERT INTO sales VALUES ('Type Z', 'F', 4000);
INSERT INTO sales VALUES ('Type Z', 'G', 3000);

Результат:

+------+-----------+-----------+---------+
| rank | cust_type | cust_name | revenue |
+------+-----------+-----------+---------+
|    1 | Type Z    | F         |    4000 |
|    2 | Type Z    | G         |    3000 |
|    1 | Type Y    | D         |    6000 |
|    2 | Type Y    | C         |    5000 |
|    3 | Type Y    | E         |    4000 |
|    1 | Type X    | B         |    8000 |
|    2 | Type X    | A         |    7000 |
+------+-----------+-----------+---------+
7 rows in set (0.00 sec)

Вы, очевидно, можете упорядочить cust_type в порядке возрастания, а не по убыванию. Я использовал спуск только для Top до Bottom в исходном тестовом примере.

Ответ 2

Я нашел проблему с решением, используя CASE, @curRow и @curType. Это зависит от плана выполнения MySQL, который используется для обработки запроса. Например, он появляется, если вы добавляете соединение к запросу. Тогда нет гарантии, что ранг будет правильно рассчитан.

Сделаем небольшое изменение в ответе:

CREATE TABLE sales (cust_type_id int, cust_name varchar(10), revenue int);
CREATE TABLE cust_type (cust_type_id int, type_name varchar(10));

INSERT INTO cust_type VALUES (1, 'Bottom');
INSERT INTO cust_type VALUES (2, 'Top');

INSERT INTO sales VALUES (2, 'A', 10000);
INSERT INTO sales VALUES (2, 'B', 9000);
INSERT INTO sales VALUES (2, 'C', 8000);
INSERT INTO sales VALUES (1, 'X', 5000);
INSERT INTO sales VALUES (1, 'Y', 6000);
INSERT INTO sales VALUES (1, 'Z', 7000);

Если я запрашиваю только таблицу sales, я получаю ранг в правильном порядке, но если я присоединяюсь к таблице cust_type, значения ранга больше не верны.

SELECT    ( 
            CASE s.cust_type_id 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := s.cust_type_id END
          ) AS rank,
          t.type_name,
          s.cust_name,
          s.revenue
FROM      sales s,
          cust_type t,
          (SELECT @curRow := 0, @curType := 0) r
WHERE s.cust_type_id = t.cust_type_id 
ORDER BY  t.type_name DESC, s.revenue DESC;

Результат:

+------+-----------+-----------+---------+
| rank | type_name | cust_name | revenue |
+------+-----------+-----------+---------+
|    1 | Top       | A         |   10000 |
|    2 | Top       | B         |    9000 |
|    3 | Top       | C         |    8000 |
|    3 | Bottom    | Z         |    7000 |
|    2 | Bottom    | Y         |    6000 |
|    1 | Bottom    | X         |    5000 |
+------+-----------+-----------+---------+

MySQL запускает начальный запрос в временную таблицу, а затем ORDER BY выполняется против таблицы temp после. Ранг уже был вычислен.

Ответ 3

Это похоже на ответ Томаса, но немного проще:

SELECT (SELECT COUNT(Cust_Type) FROM sales 
           WHERE Cust_Type = S.Cust_Type AND Revenue >= S.Revenue) AS Rank,
        Cust_Type,
        Cust_Name,
        Revenue 
  FROM sales AS S
  ORDER BY Cust_Type DESC, Rank;

Я не уверен, как производительность сравнивается с решением Daniel, особенно на очень больших наборах данных, или если вам нужно использовать сложные объединения.

Ответ 4

Что не совсем ясно, так это то, как элементы должны быть ранжированы (я предполагал Revenue) или вы только вытаскиваете определенное количество значений (например, верхние 3 и нижние 3), поэтому я предположил, что вы хотите все значения. Учитывая эти предположения,

Select Cust_Name, Cust_Type
    , (Select Count(*)
        From Table As T1
        Where T1.Revenue > T.Revenue ) + 1 As Rank
From Table As T
Where Cust_Type = 'Top'
Union All
Select Cust_Name, Cust_Type
    , (Select Count(*)
        From Table As T1
        Where T1.Revenue < T.Revenue ) + 1 As Rank
From Table As T
Where Cust_Type = 'Bottom'

Если вы пытались сделать это в одном неединичном запросе, вы могли бы сделать:

Select Cust_Name, Cust_Type
    , Case Z.Cust_Type
        When 'Top' Then Z.TopRank
        Else Z.BottomRank
        End As Rank
From    (
        Select Cust_Name, Cust_Type
            , (Select Count(*)
                From Table As T1
                Where T1.Revenue > T.Revenue ) + 1 As TopRank
            , (Select Count(*)
                From Table As T1
                Where T1.Revenue < T.Revenue ) + 1 As BottomRank
        From Table As T
        ) As Z