MySql分组查询规则

系统管理员 2022-11-14 14:37 239阅读 0赞

### 文章目录 ###

*   *  一、问题
     *  二、结论证明
     *   *   *  实验一
             *  实验二

## 一、问题 ##

> **思考一下假如你有一个这样的需求**  
>   
> 有一张账单表（bill\_detail），一张开票表（open\_invoice）  
> 每一个用户有三种费用类型，分别是水费、污水费、违约金。（分别对应cost\_type = 01、02、03）  
> 现在要查询每个用户的欠费总额，和是否开票。（注：只有水费可以开票，其它的不需要）

**账单表（bill\_detail）**

CREATE TABLE `bill_detail` (
    	`id` VARCHAR(50) NOT NULL COMMENT 'id',
      	`cost_type` VARCHAR(2) NOT NULL COMMENT '费用类型 01 水费、02 污水费、03 违约金',
    	`amount_money` FLOAT(10,2) NOT NULL COMMENT '欠费金额',
    	`user_name` VARCHAR(20)  NOT NULL COMMENT '用户名',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='账单明细表';

**开票表（open\_invoice）**

CREATE TABLE `open_invoice` (
     	`id` VARCHAR(50) NOT NULL COMMENT 'id',
      	`bill_detail_id` VARCHAR(50) NOT NULL COMMENT '账单明细id',
    	`open_invoice_status` INT(1) NOT NULL COMMENT '开票状态（1 已开票， 0 未开票）',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开票表';

**方法一**

我们知道group by是取分组后的第一条数据，所以我们只需要把**bill\_detail**表进行排序，这样每一组（用户名分组）数据，费用类型为 **01**就是第一个了

SELECT
    	sum(amount_money) money,
    	invoice.open_invoice_status
    FROM
    	(SELECT * FROM bill_detail ORDER BY cost_type ASC) bill 
    	LEFT JOIN open_invoice invoice ON bill.id = invoice.bill_detail_id
    GROUP BY bill.user_name

**方法二**

SELECT
    	sum(amount_money) money,
    	invoice.open_invoice_status
    FROM
    	bill_detail bill 
    	LEFT JOIN open_invoice invoice ON bill.id = invoice.bill_detail_id
    GROUP BY bill.user_name

**分组(group by)后，会选择第一条数据，连接查询会改变原本的查询顺序。（ON后面的字段有值的排在前面，null在后面）**

因为我们题目说了，只有水费为01的才会开票，所以连接查询后01这条数据一定会是第一个，无需先排序。

## 二、结论证明 ##

> 有一张用户表（user），和一张女朋友表（girl\_friend）  
> 我们要查询出来每个人的id、用户名、女朋友名称  
>   
> 注：我们知道不是每一个人都有女朋友的

**用户表(user)**

CREATE TABLE `user` (
      `id` varchar(50) NOT NULL COMMENT 'id',
      `user_name` varchar(20) NOT NULL COMMENT '用户名',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户';

**女朋友表(girl\_friend)**

CREATE TABLE `girl_friend` (
      `id` varchar(50) NOT NULL COMMENT 'id',
      `user_id` varchar(20) NOT NULL COMMENT '所属用户',
      `girl_name` varchar(20) NOT NULL COMMENT '女朋友名',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='女朋友表';

**查询sql**

SELECT
    	user.id,
    	user.user_name,
    	girl.girl_name
    FROM user 
    LEFT JOIN girl_friend girl ON user.id = girl.user_id

#### 实验一 ####

当我们的表数据如下：

**查询结果如下：**

#### 实验二 ####

用户表不变，女朋友表数据如下

**查询结果如下：**