基于 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
这样，运营人员拿到商品就可以分析出哪些商品是需要赶紧补货的。
