一张桌子中有多个等级?mysql

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

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在原来的测试用例。来源：stack overflow