一张桌子中有多个等级 [英] Multiple Ranks in one table

问题描述

我需要以下内容，任何人都可以帮我做到这一点.

I need the following, Can anyone please help me do it.

Rank   Cust_Type   Cust_Name   Revenue
1      Top         A           10000
2      Top         B           9000
3      Top         C           8000
1      Bottom      X           5000
2      Bottom      Y           6000
3      Bottom      Z           7000

我需要对Cust_Type的顶部和底部分别进行排名，而所有这些都在MySQL中.

I need separate ranks for Top and Bottom Cust_Type and all this is in MySQL.

推荐答案

这有点棘手.您可能要使用变量，例如下面的示例:

This is a bit tricky. You may want to use variables, such as in the following example:

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命令.

The (SELECT @curRow := 0, @curType := '') r part allows the variable initialization without requiring a separate SET command.

测试用例:

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之前.

You can obviously order the cust_type in ascending order instead of descending. I used descending just to have Top before Bottom in the original test case.

这篇关于一张桌子中有多个等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！