MySQL grouping query rules

One 、 problem

Think about if you have such a need

There's a bill （bill_detail）, A billing form （open_invoice）
Each user has three fee types , They are water charges 、 Sewage fee 、 Liquidated damages .（ They correspond to each other cost_type = 01、02、03）
Now you need to query the total amount of arrears of each user , And whether to issue invoice .（ notes ： Only water charges can be invoiced , Others don't need ）

Bill form （bill_detail）

CREATE TABLE `bill_detail` (
	`id` VARCHAR(50) NOT NULL COMMENT 'id',
  	`cost_type` VARCHAR(2) NOT NULL COMMENT ' Fee type  01  charge for water 、02  Sewage fee 、03  Liquidated damages ',
	`amount_money` FLOAT(10,2) NOT NULL COMMENT ' The amount owed ',
	`user_name` VARCHAR(20)  NOT NULL COMMENT ' user name ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' Bill details ';

Billing form （open_invoice）

CREATE TABLE `open_invoice` (
 	`id` VARCHAR(50) NOT NULL COMMENT 'id',
  	`bill_detail_id` VARCHAR(50) NOT NULL COMMENT ' Bill details id',
	`open_invoice_status` INT(1) NOT NULL COMMENT ' Billing status （1  Invoiced , 0  Not invoiced ）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' Billing form ';

Method 1

We know group by Is the first data after grouping , So we just need to put bill_detail Sort table , So each group （ User name grouping ） data , The expense type is 01 That's the first one

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

Method 2

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

grouping (group by) after , Will select the first data , Join query will change the original query order .（ON The following fields with values are listed in the front ,null rearwards ）

Because our topic says , Only the water charge is 01 Will be billed , So after connection query 01 This data must be the first , No need to sort first .

Two 、 The conclusion proves that

There's a list of users （user）, And a girlfriend list （girl_friend）
We need to find out everyone's id、 user name 、 Girlfriend name

notes ： We know that not everyone has a girlfriend

User table (user)

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

Girlfriend watch (girl_friend)

CREATE TABLE `girl_friend` (
  `id` varchar(50) NOT NULL COMMENT 'id',
  `user_id` varchar(20) NOT NULL COMMENT ' Users ',
  `girl_name` varchar(20) NOT NULL COMMENT ' Girlfriend name ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' Girlfriend watch ';

Inquire about sql

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

Experiment 1

When our table data is as follows ：

id	user_name
1	Zhang San
2	Li Si
3	Wang Wu

id	user_id	girl_name
1	1	lotus

The query results are as follows ：

id	user_name	girl_name
1	Zhang San	lotus
2	Li Si
3	Wang Wu

Experiment two

The user table does not change , The data of girlfriend table is as follows

id	user_id	girl_name
1	2	Big Joe
1	2	Little Joe

The query results are as follows ：

id	user_name	girl_name
2	Li Si	Big Joe
3	Wang Wu	Little Joe
1	Zhang San