mysql多表联合查询分页查询结果条数错误问题

亦凉 2023-10-09 12:41 129阅读 0赞

### 文章目录 ###

*   *   *  1 摘要
         *  2 情景复现
         *   *  2.1 添加测试数据
             *  2.2 错误的分页查询
             *  2.3 正确的分页查询

### 1 摘要 ###

在项目中，多表联合查询是最常见的情景之一，在多表联合查询同时进行分页查询时，可能会出现查询结果条数不正确的情况或者说查询结果有遗漏的情况。针对这种问题，本文将探讨其解决方案。

### 2 情景复现 ###

#### 2.1 添加测试数据 ####

本文将继续以  
[MySQL SELECT COUNT 一对多关联查询去重][MySQL SELECT COUNT]  
博客中的数据库为例

现有用户 `5` 名

-- 查询用户数量
    SELECT COUNT(*) FROM `user`;

![查询用户总数][20190925203031523.png]

-- 查询所有用户
    SELECT * FROM `user`;

![查询所有用户][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70]

有用户图像 `12` 条

-- 查询用户图像数量
    SELECT COUNT(*) FROM `user_photo`;

![查询用户图像数量][20190925203629531.png]

-- 查询所有用户图像
    SELECT * FROM `user_photo`;

![查询所有用户图像][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70 1]

#### 2.2 错误的分页查询 ####

需求: 使用分页查询,每页显示 5 条数据,根据用户 id 进行分组

预期结果: 显示5个用户的信息,目标用户关联的图像也都在查询结果中

实际查询:

-- 分页查询用户信息(包含用户图像)
    SELECT IFNULL(u.id,0), IFNULL(u.user_name,''), IFNULL(u.user_email,''), IFNULL(up.photo_url,'')
    FROM `user` u
    LEFT JOIN `user_photo` up ON up.user_id = u.id
    GROUP BY u.id
    ORDER BY u.id ASC
    LIMIT 0,5

查询结果:

![错误的分页查询][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70 2]  
查询结果的确是 `5` 条，但是每个用户的图像都最多是 `1` 条，实际的对应关系中部分用户的图像是多个，即一对多的关系，这样的查询结果不符合实际预期

#### 2.3 正确的分页查询 ####

针对以上问题,**解决办法为使用子查询的方式，在主表的子查询中进行分页查询**  
对应的 SQL:

-- 分页查询用户信息(包含用户图像)
    SELECT IFNULL(u.id,0), IFNULL(u.user_name,''), IFNULL(u.user_email,''), IFNULL(up.photo_url,'')
    FROM (
        SELECT id, user_name, user_email
        FROM `user`
        ORDER BY id
        LIMIT 0,5
    ) AS u
    LEFT JOIN `user_photo` up ON up.user_id = u.id

查询结果:

![正确的多表李兰河查询分页查询][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70 3]  
这才是预期中查询结果

个人公众号:**404Code**,记录半个互联网人的技术与思考，感兴趣的可以关注.  
![404Code][]

[MySQL SELECT COUNT]: https://blog.csdn.net/Mrqiang9001/article/details/100180644
[20190925203031523.png]: https://img-blog.csdnimg.cn/20190925203031523.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190925203313136.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx,size_16,color_FFFFFF,t_70
[20190925203629531.png]: https://img-blog.csdnimg.cn/20190925203629531.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190925203746271.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190925204428838.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190925204755132.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx,size_16,color_FFFFFF,t_70
[404Code]: https://img-blog.csdn.net/20181010223005114?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01ycWlhbmc5MDAx/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70