主页 / dba / 问题 / 182909
Accepted
Alw
Asked: 2017-08-08 16:40:32 +0800 CST

点餐后获取水果排名

  • 772

我有下表，称为sales

+--------+--------+
| fruit  | amount |
+--------+--------+
| cherry |    124 |
| plum   |     23 |
| pear   |     19 |
| apple  |     13 |
| banana |      4 |
| orange |      2 |
+--------+--------+

并用它来获得排名

SET @rank=0; 
SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC;

.

+------+--------+--------+
| rank | fruit  | amount |
+------+--------+--------+
|    1 | cherry |    124 |
|    2 | plum   |     23 |
|    3 | pear   |     19 |
|    4 | apple  |     13 |
|    5 | banana |      4 |
|    6 | orange |      2 |
+------+--------+--------+

所以现在我想获得的排名，apple但我不知道该怎么做。

mysql rank

1 个回答

  1. Best Answer

    您可以将查询放在子查询中并对它执行 WHERE

    提出查询

    SET @rank=0; 
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

    样本数据

    DROP DATABASE IF EXISTS Alw7SHxD;
CREATE DATABASE Alw7SHxD;
USE Alw7SHxD
CREATE TABLE sales
(
    id INT NOT NULL AUTO_INCREMENT,
    fruit VARCHAR(20),
    amount INT DEFAULT 0,
    PRIMARY KEY (id)
);
INSERT INTO sales (fruit,amount) VALUES
('cherry',124), ('plum'  , 23), ('pear'  , 19),
('apple' , 13), ('banana',  4), ('orange',  2);
SELECT * FROM sales;
SET @rank=0; 
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

    执行的示例代码

    mysql> DROP DATABASE IF EXISTS Alw7SHxD;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE Alw7SHxD;
Query OK, 1 row affected (0.00 sec)

mysql> USE Alw7SHxD
Database changed
mysql> CREATE TABLE sales
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fruit VARCHAR(20),
    ->     amount INT DEFAULT 0,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO sales (fruit,amount) VALUES
    -> ('cherry',124), ('plum'  , 23), ('pear'  , 19),
    -> ('apple' , 13), ('banana',  4), ('orange',  2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sales;
+----+--------+--------+
| id | fruit  | amount |
+----+--------+--------+
|  1 | cherry |    124 |
|  2 | plum   |     23 |
|  3 | pear   |     19 |
|  4 | apple  |     13 |
|  5 | banana |      4 |
|  6 | orange |      2 |
+----+--------+--------+
6 rows in set (0.00 sec)

mysql>

    执行的建议查询

    mysql> SET @rank=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT rank FROM
    -> (SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
    -> WHERE fruit='apple';
+------+
| rank |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>

    试试看 ！！！

    • 0

相关问题