Múltiples ranges en una table

Esto es un poco complicado. Es posible que desee utilizar variables, como en el siguiente ejemplo:

 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; 

La parte (SELECT @curRow := 0, @curType := '') r permite la initialization de la variable sin requerir un command SET separado.

Caso de testing:

 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); 

Resultado:

 +------+-----------+-----------+---------+ | 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) 

Otro caso de testing:

 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); 

Resultado:

 +------+-----------+-----------+---------+ | 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) 

Obviamente puedes orderar el cust_type en order ascendente en lugar de descender. Solía ​​descender solo para tener Top antes de Bottom en el caso de testing original.

Encontré un problema con la solución usando CASE , @curRow y @curType . Depende del plan de ejecución que MySQL utiliza para procesar la consulta. Por ejemplo, aparece si agrega un join a la consulta. Entonces no hay garantía de que el range vaya a ser calculado correctamente.

Haciendo un ligero cambio en la respuesta:

 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); 

Si consulto solo la tabla de sales obtengo el range en el order correcto, pero si me uno a la tabla cust_type los valores de range ya no son correctos

 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; 

Resultado:

 +------+-----------+-----------+---------+ | 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 está ejecutando la consulta inicial en una tabla temporal y luego el ORDER BY está ejecutando contra la tabla temporal después de que ya se calculó un range.

Esto es similar a la respuesta de Thomas, pero un poco más simple:

 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; 

No estoy seguro de cómo se compara el performance con la solución de Daniel, particularmente en sets de datos muy grandes, o si tiene que usar combinaciones complejas.

Lo que no está exactamente claro es cómo deben clasificarse los artículos (supongo por Ingresos) o si solo está obteniendo un cierto número de valores (por ejemplo, los 3 superiores y los 3 inferiores), así que asumí que quería todos los valores. Dados esos supuestos,

 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' 

Si estuviera tratando de hacer esto en una única consulta no sindical, podría hacer lo siguiente:

 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