基于 MySQL 5.7，这是程序员在囧途的课程笔记，如果需要的话可以关注一下这个网站，有很多 PHP 相关的学习资料，而且很接地气，没有任何复杂概念，博主个人很喜欢。

01 场景案例：实现 select 增加行号

很多时候我们在做一个项目，无论使用的语言是 PHP 还是 Java，不管是为了性能还是业务逻辑，很多时候遇到的瓶颈并不在语言上，而是在数据库上，当然还有一些其他第三方工具，在这里就不进行讲解了，这里的重点是 SQL 思维培养。

首先我们准备一下 SQL。

create table products
(
    p_id   bigint auto_increment comment '产品 id',
    p_name varchar(100)     not null comment '商品名称',
    p_type varchar(50)      not null comment '商品分类，为了演示方便直接使用中文',
    p_view bigint default 0 not null comment '点击量',
    constraint products_pk
        primary key (p_id)
) comment '产品表';

我们现在假设按照点击量进行倒序排列。

select p_name, p_view, p_type
from products
order by p_view desc;

这样就可以按照点击量的顺序取出数据了，接下来我们来显示行号。如果使用过Access 或者 SQl Server 的话它们提供了显示行号的方法，在 MySQL中并没有这样的方法。我们只能够自己实现。

我们首先来引入一个会话变量，我们先来看一下什么是会话变量。

我们可以通过 set 来设置变量：

set @name := 'maksim';
select @name

这样我们就可以在当前绘画中来获取变量了。

这里注意，会话变量只在当前会话有效，当你打开新的会发现获取不到变量的情况。

我们可以利用变量进行累加操作这样就可以实现行号了。

# 设置变量
set @rownum = 0;

# 利用累加实现编号
select p_name, p_view, p_type, @rownum := @rownum + 1
from products
order by p_view desc;

我们不可能每次查询都要重新设置一下@rownum，其实我们可以借助 IFNULL 函数来实现在一条语句中实现获取行号的操作。

• IFNULL(expr1, expr2) expr1 不为 null，则返回值为 expr1，反之为 expr2

select p_name, p_view, p_type, IFNULL(@rownum := @rownum + 1, @rownum := 1) as rownum
from products
order by p_view desc;

但是现在还有一个问题，当我们再次执行获取数据的语句时候会出现下面的状况。

解决这个问题有两种方案，首先是在每次执行前都重置会话变量，但是这样并没有什么意义。

还有另外一个解决方案，我们可以在关联表后面增加一条语句，对@rownum 进行初始化。

select p_name, p_view, p_type,IFNULL(@rownum := @rownum + 1, @rownum := 1) as rownum
from products,
     (select @rownum := 0) b
order by p_view desc;

这样我们每次执行的结果就都是正确的了。

02 场景案例：分组后在分组内排序、每个分组中取前N条

这个需求在我们做项目的时候经常会碰到，有的时候 SQL 写不出来就会用 ORM 框架反复折腾，这个需求需要配合我们的 SQL 来进行完成。在一些复杂系统中仅仅会一些 ORM 框架是没有用的。

我们依旧使用上节中的数据结构，首先我们取出一个分组后排序的数据。

如果显示 group by 错误，无法显示p_view，可以参考下方链接

一文带你了解mysql sql model的only_full_group_by模式含Error 1055问题分析_ShenLiang2025的博客-CSDN博客

select p_type, p_name, p_view
from products
group by p_type, p_name
order by p_view desc ;

我们可以看到为了满足 p_view的倒排序,列表中类型出现了交叉，分组就没有了意义。为了让类型不交叉，我们只需要在增加 p_type 的排序就可以了。

接下来，我们对其增加序号。

select IF(@back = p_type, @rownum := @rownum + 1, @rownum := 1) as row_num,
       p_type,
       p_name,
       p_view,

       @back := p_type
from (select p_type, p_name, p_view
      from products
      group by p_type, p_name
      order by p_type, p_view desc) a,
     (select @rownum := 0, @bak := '') b;

到这里，你是不是就发现了什么，对，没错，其实这就是我们常见的排行榜。

接下里我们增加单独去两行的操作。

select row_num, p_type, p_name, p_view
from (select IF(@back = p_type, @rownum := @rownum + 1, @rownum := 1) as row_num,
             p_type,
             p_name,
             p_view,

             @back := p_type
      from (select p_type, p_name, p_view
            from products
            group by p_type, p_name
            order by p_type, p_view desc) a,
           (select @rownum := 0, @bak := '') b) c
where c.row_num <= 2

03 场景案例：纯 SQL 也能实现辅助决策统计

如果你只会使用 ORM，那么很多复杂的功能根本做不了，今天我们来实现一个纯 SQL 计算商品的重要度，是否需要进行补货。

很多人认为我们开发 web 只需要增删改查就好了，但是其实如果我们要去做电商平台，或者后台系统，最常见的需求就是统计分析，仅仅是显示商品列表，编辑一下，就很低级。

在 products 表中有点击量这个字段，如果我们工具点击量来依次为依据来判断他为热销商品，那肯定是不对的，因为我们还要去看销售量，在这里我们需要增加一张 products_sales表，来代表销售。

在这里有一个细节，加入我们的商品有那么些，我们的销量就只有几个，所以说并不是所有的商品都有销量。我们不能光靠点击量和销售量当个为依据来进行辅助决策。所以在做后台应用的时候要复杂很多。

我们通过点击量和销售量来做一个评分，然后根据评分辅助决策。

现在我们写一个 SQL，根据分类显示出商品的名称、点击量和销售量情况。没有销售的设置为 0.

select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
from products as a
         left join products_sales b on a.p_id = b.p_id
group by a.p_type, a.p_name
order by a.p_type desc, a.p_view desc;

接下来，我们来计算分类的平均销量。

select p_type, sum(sales) / count(*)
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a
group by p_type

如果需要控制小数点，我们可以增加 round。

select p_type, round(sum(sales) / count(*), 0)
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a
group by p_type

其实这个地方还有问题，那就是我们需要剔除那些没有销量的商品。

select p_type, round(sum(sales) / count(*), 0) as sales_avg
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a
where a.sales > 0
group by p_type

接下来我们计算点击量的平均值。

select p_type, round(sum(p_view) / count(*)) as view_avg
from products
group by p_type

再之后我们将两个 SQL 合并

select a.p_type, p_name, p_view, view_avg, sales, sales_avg
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a,
     (select p_type, round(sum(p_view) / count(*)) as view_avg
      from products
      group by p_type) b,
     (select p_type, round(sum(sales) / count(*), 0) as sales_avg
      from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
            from products as a
                     left join products_sales b on a.p_id = b.p_id
            group by a.p_type, a.p_name
            order by a.p_type desc, a.p_view desc) a
      where a.sales > 0
      group by p_type) c
where a.p_type = b.p_type
  and a.p_type = c.p_type

既然数据我们都有了，就可以进行下一步计算评分了。

我们通过点击量/点击量平均值+销量/销量平均值。

select a.p_type, p_name, p_view / view_avg, sales / sales_avg
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a,
     (select p_type, round(sum(p_view) / count(*)) as view_avg
      from products
      group by p_type) b,
     (select p_type, round(sum(sales) / count(*), 0) as sales_avg
      from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
            from products as a
                     left join products_sales b on a.p_id = b.p_id
            group by a.p_type, a.p_name
            order by a.p_type desc, a.p_view desc) a
      where a.sales > 0
      group by p_type) c
where a.p_type = b.p_type
  and a.p_type = c.p_type

select a.p_type, p_name, (p_view / view_avg + sales / sales_avg)
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a,
     (select p_type, round(sum(p_view) / count(*)) as view_avg
      from products
      group by p_type) b,
     (select p_type, round(sum(sales) / count(*), 0) as sales_avg
      from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
            from products as a
                     left join products_sales b on a.p_id = b.p_id
            group by a.p_type, a.p_name
            order by a.p_type desc, a.p_view desc) a
      where a.sales > 0
      group by p_type) c
where a.p_type = b.p_type
  and a.p_type = c.p_type

这个时候问题出现了，明明是五花肉卖出去的更多，但是猪肉一个没卖出去，平分反倒更高了。所以我们需要对算法增加系数，也就是权重。

select a.p_type, p_name, (p_view / view_avg) * 0.2 + (sales / sales_avg * 0.8)
from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
      from products as a
               left join products_sales b on a.p_id = b.p_id
      group by a.p_type, a.p_name
      order by a.p_type desc, a.p_view desc) a,
     (select p_type, round(sum(p_view) / count(*)) as view_avg
      from products
      group by p_type) b,
     (select p_type, round(sum(sales) / count(*), 0) as sales_avg
      from (select p_type, a.p_name, a.p_view, IFNULL(b.p_sales, 0) as sales
            from products as a
                     left join products_sales b on a.p_id = b.p_id
            group by a.p_type, a.p_name
            order by a.p_type desc, a.p_view desc) a
      where a.sales > 0
      group by p_type) c
where a.p_type = b.p_type
  and a.p_type = c.p_type

这样，运营人员拿到商品就可以分析出哪些商品是需要赶紧补货的。