MySQL自定义变量和排序的使用--以查询每个分类的最高销量商品为例

---

前言

今天在工作中遇到了MySQL数据库的自定义变量，可以用于实现类Oracle数据库的rownum一级其他一些更复杂的功能。

一、背景

MySQL的自定义变量是一种用户可以定义和修改的变量。自定义变量的名称以@符号开头，例如@myvar。自定义变量可以存储一个值，也可以存储一个表达式的结果。通过不同的表达式，可以为每一条数据都生产不同的自定义数据。比如Oracle中的rownum等。

二、场景

假如有商品销量表，结构如下：

列名	中文名
id	主键id
name	商品名称
type	商品类型
sales	商品销量

需求：查询每个类别销量最高的一种商品的名称和销量。

三.准备工作

使用如下SQL进行创建表和测试数据的插入：

-- 创建表
CREATE TABLE Products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    sales INT NOT NULL
);

-- 插入测试数据
INSERT INTO Products (name, type, sales) VALUES
('Product 1', 'Type A', 100),
('Product 2', 'Type A', 200),
('Product 3', 'Type B', 150),
('Product 4', 'Type B', 250),
('Product 5', 'Type C', 75),
('Product 6', 'Type C', 175),
('Product 7', 'Type A', 225),
('Product 8', 'Type B', 300),
('Product 9', 'Type C', 80),
('Product 10', 'Type A', 90);

四.解决方案

查询SQL如下：

SELECT result.id, result.name, result.type, result.sales FROM (
    SELECT 
        products.*,
        @rownum := IF(@prev_type = type, @rownum + 1, 1) AS rn,
        @prev_type := type
    FROM
        (SELECT * FROM Products ORDER BY type, sales DESC) as products,
        (SELECT @rownum := 0, @prev_type := '') as vars
) as result
WHERE result.rn = 1;

具体思路为
1.从products表中查询需要的商品名称、类型、销量等数据，并按照类型、销量排序，保证每种类型的商品在结果中都是相邻的。

(SELECT * FROM Products ORDER BY type, sales DESC) as products

2.使用自定义数据，记为@rownum,@prev_type，其中@rownum表示记录的行号初始默认为0，而@prev_type表示上一条查询记录的商品类型，初始默认null。然后与products的查询结果进行（隐式）连接，即给products的每条查询结果都添加了初始的@rownum和@prev_type。该部分的查询单独执行结果为：

SELECT 
        *
    FROM
        (SELECT * FROM Products ORDER BY type, sales DESC) as products,
        (SELECT @rownum := 0, @prev_type := '') as vars

查询的结果为：

id	name	type	sales	@rownum :=0	@prev_type := “”
7	Product 7	Type A	225	0
2	Product 2	Type A	200	0
1	Product 1	Type A	100	0
10	Product 10	Type A	90	0
8	Product 8	Type B	300	0
4	Product 4	Type B	250	0
3	Product 3	Type B	150	0
6	Product 6	Type C	175	0
9	Product 9	Type C	80	0
5	Product 5	Type C	75	0

3.在外层的查询中， 因为已经按找类型进行了排序，因此在结果中，type相同的数据行必定相邻，可以在每一条记录中进行检查。我们首先检查自定义变量@prev_type的值是否和本条记录，如果相同，那么我们吧@rownum+1作为记录的行号，否则说明记录已经来到了新的类型，我们将@rownum归零，重新从1开始计算.这样就可以得到每种类型的商品在自己所属的类型内按销量先行排序的结果。

SELECT 
        products.*,
        @rownum := IF(@prev_type = type, @rownum + 1, 1) AS rn,
        @prev_type := type
    FROM
        (SELECT * FROM Products ORDER BY type, sales DESC) as products,
        (SELECT @rownum := 0, @prev_type := '') as vars

查询的结果如下：

id	name	type	sales	rn	@prev_type := “”
7	Product 7	Type A	225	1	Type A
2	Product 2	Type A	200	2	Type A
1	Product 1	Type A	100	3	Type A
10	Product 10	Type A	90	4	Type A
8	Product 8	Type B	300	1	Type B
4	Product 4	Type B	250	2	Type B
3	Product 3	Type B	150	3	Type B
6	Product 6	Type C	175	1	Type C
9	Product 9	Type C	80	2	Type C
5	Product 5	Type C	75	3	Type C

4.最后只要在结果中筛选rn=1的记录，即可得到每种商品类型内销量为第一的商品

SELECT result.id, result.name, result.type, result.sales FROM (
    SELECT 
        products.*,
        @rownum := IF(@prev_type = type, @rownum + 1, 1) AS rn,
        @prev_type := type
    FROM
        (SELECT * FROM Products ORDER BY type, sales DESC) as products,
        (SELECT @rownum := 0, @prev_type := '') as vars
) as result
WHERE result.rn = 1;

查询结果为：

id	name	type	sales
7	Product 7	Type A	225
8	Product 8	Type B	300
6	Product 6	Type C	175

总结

本文以查询不同类型商品的最高销量记录为例简单介绍了MySQL
中自定义数据的使用。实际生产中，可以根据不同需要定制SQL，也可以根据场所的需要选择其他更符合需求的数据库。