外连接报表需求处理

---

业务需求

写一个报表：
包含两种数据：发票、订单
两种数据的关联：两个数据都有公司信息
需求：根据公司进行分组，展示发票和订单信息在一个报表里。

---

一、方案一：需求

告诉业务最好写两个报表

二、方案二：代码

使用代码进行开发，不用一个SQL输出

三、方案三：SQL

0、需求的数据展示

订单表：

order_id	company	order_amount	date
1	a	1	2023-11-29
2	a	2	2023-11-30
3	b	2	2023-11-30

发票表：

invoice_id	company	invoice_amount	status
1	a	1	S
2	a	2	E
3	b	2	E

最终报表展示：
date = 2023-11-29，status = E 的数据
满足条件的数据：order_id: 1; invoice_id: 2, 3

company	order_amount	invoice_amount
a	1	2
b	0	2

1、使用外连接：FULL OUTER JOIN

注意：
MySQL不支持外连接。
不能直接用外连接把两个表连起来再筛选。需要先筛选出来，然后连接起来。

SELECT
IF (TEMP_ORDER.COMPANY IS NULL, TEMP_INVOICE.COMPANY, TEMP_ORDER.COMPANY) COMPANY,
TEMP_ORDER.ORDER_AMOUNT,
TEMP_INVOICE.INVOICE_AMOUNT
FROM (
	SELECT
		COMPANY, 
		SUM(ORDER_AMOUNT) ORDER_AMOUNT
	FROM ORDER
	WHERE DATE = #{DATE}
	GROUP BY COMPANY
) TEMP_ORDER
FULL OUTER JOIN (
	SELECT 
		COMPANY,
		SUM(INVOICE_AMOUNT) INVOICE_AMOUNT
	FROM INVOICE
	WHERE STATUS = #{STATUS}
	GROUP BY COMPANY
) TEMP_INVOICE ON TEMP_ORDER.COMPANY = TEMP_INVOICE.COMPANY

2、使用左连接和右连接：LEFT JOIN, RIGHT JOIN

注意：
不能直接把两个表连起来再筛选。需要先筛选出来，然后连接起来。
这里使用UNION连接起来，因为UNION会去除重复部分，UNION ALL 不会。

SELECT
TEMP_ORDER.COMPANY,
TEMP_ORDER.ORDER_AMOUNT,
TEMP_INVOICE.INVOICE_AMOUNT
FROM (
	SELECT
		COMPANY, 
		SUM(ORDER_AMOUNT) ORDER_AMOUNT
	FROM ORDER
	WHERE DATE = #{DATE}
	GROUP BY COMPANY
) TEMP_ORDER
LEFT JOIN (
	SELECT 
		COMPANY,
		SUM(INVOICE_AMOUNT) INVOICE_AMOUNT
	FROM INVOICE
	WHERE STATUS = #{STATUS}
	GROUP BY COMPANY
) TEMP_INVOICE ON TEMP_ORDER.COMPANY = TEMP_INVOICE.COMPANY

UNION 

SELECT
TEMP_INVOICE.COMPANY,
TEMP_ORDER.ORDER_AMOUNT,
TEMP_INVOICE.INVOICE_AMOUNT
FROM (
	SELECT
		COMPANY, 
		SUM(ORDER_AMOUNT) ORDER_AMOUNT
	FROM ORDER
	WHERE DATE = #{DATE}
	GROUP BY COMPANY
) TEMP_ORDER
RINGHT JOIN (
	SELECT 
		COMPANY,
		SUM(INVOICE_AMOUNT) INVOICE_AMOUNT
	FROM INVOICE
	WHERE STATUS = #{STATUS}
	GROUP BY COMPANY
) TEMP_INVOICE ON TEMP_ORDER.COMPANY = TEMP_INVOICE.COMPANY

四、其他

1、SQL 的 jion 方式
https://www.runoob.com/w3cnote/sql-join-image-explain.html
2、union 和 union all 的区别
https://www.php.cn/faq/552552.html