工作日常：如何用SQL实现两个表数据的差集操作？

经过一段时间的间隔，我尚未更新OMS系统的资料整理。因此，我决定先撰写一篇关于SQL查询功能的文章，该功能刚在今日完成。

需求描述

需要执行一个查询，以获取上周与上上周之间药品采购量数据的差异，具体返回结果应包含药品名称及其数量差异。

补充说明

• 查询的时间范围应定义为从周一至周日，而非基于当前日期向前推算的七天。

• 在两个不同的时间段内，检索出的数据可能并不完全一致；即第一个时间段内存在的药品在第二个时间段内可能不存在，反之亦然。

• 对于每种药品，可能存在多条数据记录。

解决思路

上周一到周日sql

SELECT * FROM your_table_name
WHERE date_column >= DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY), '%Y-%m-%d 00:00:00') 
AND date_column < DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00');

• WEEKDAY(CURDATE()) 函数返回今天是周几，其中周一为0，周日为6。

• DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 7 DAY) 会先找到本周一的日期，然后再往前回溯7天到上周一。

• DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) 找到本周一的日期，用作上周数据的结束时间（不包含这一天）。

上上周一到周日的sql

-- 上上周一
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 14 DAY) AS '上上周一';

-- 上上周日
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 8 DAY) AS '上上周日';

根据今天的日期（2024年02月27日，星期二），这两条SQL语句将帮助你计算出上上周周一和周日的日期。然后，你可以使用这些日期作为过滤条件来查询特定数据。例如：​​​​​​​

假设你要查询的表名为your_table，时间字段名为date_column
SELECT * FROM your_table 
WHERE date(date_column) >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 14 DAY) 
AND date(date_column) <= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 8 DAY);

数据连接计算差值

通过IFNULL或者COALESCE函数来处理那些可能不存在于某一个表中的记录。假设您有两个表Table1和Table2，两者都包含name字段和value字段，我们的目标是将两个表根据name字段进行关联，然后对相同name的记录，进行value值的相减操作，并且保留两个表中存在的所有name值。

对Table2进行左连接，这样可以保证Table1中的所有记录都会被选出来，即使在Table2中没有匹配的name字段。COALESCE函数确保了，如果Table2中没有对应的value，则将其视为0。

使用abs函数将差值转为绝对值，最后可使用order by进行排序

SELECT
	abs(
	COALESCE ( t1.count, 0 ) - COALESCE ( t2.count, 0 )) count,
	COALESCE ( t1.medicine_name, t2.medicine_name ) medicine_name 
FROM
	(
	SELECT
		count(*) count,
		wrd.medicine_name 
	FROM
		base_medicine_info bmi
		LEFT JOIN warehouse_receipt_detail wrd ON bmi.CODE = wrd.medicine_code 
		AND bmi.available = 1 
		AND bmi.monitoring_signs = 1
		LEFT JOIN warehouse_receipt wr ON wr.id = wrd.parent_id 
		AND wr.from_price_change = 0 
		AND wr.from_give_back = 0 
	WHERE
		wrd.create_time IS NOT NULL 
		AND wrd.create_time >= DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL WEEKDAY( CURDATE()) + 7 DAY ), '%Y-%m-%d' ) 
		AND wrd.create_time < DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL WEEKDAY( CURDATE()) DAY ), '%Y-%m-%d' ) 
	GROUP BY
		wrd.medicine_name 
	) t1
	LEFT JOIN (
	SELECT
		count(*) count,
		wrd.medicine_name 
	FROM
		base_medicine_info bmi
		LEFT JOIN warehouse_receipt_detail wrd ON bmi.CODE = wrd.medicine_code 
		AND bmi.available = 1 
		AND bmi.monitoring_signs = 1
		LEFT JOIN warehouse_receipt wr ON wr.id = wrd.parent_id 
		AND wr.from_price_change = 0 
		AND wr.from_give_back = 0 
	WHERE
		wrd.create_time IS NOT NULL 
		AND wrd.create_time >= DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL WEEKDAY( CURDATE()) + 14 DAY ), '%Y-%m-%d' ) 
		AND wrd.create_time < DATE_FORMAT( DATE_SUB( CURDATE(), INTERVAL WEEKDAY( CURDATE()) + 8 DAY ), '%Y-%m-%d' ) 
	GROUP BY
	wrd.medicine_name 
	) t2 ON t1.medicine_name = t2.medicine_name
	ORDER BY count desc

诗词

兔罝

肃肃兔罝，椓之丁丁。

赳赳武夫，公侯干城。

肃肃兔罝，施于中逵。

赳赳武夫，公侯好仇。

肃肃兔罝，施于中林。

赳赳武夫，公侯腹心。

原文公众号：华仔的项目记录https://mp.weixin.qq.com/s?__biz=MzkyNjY0MjA0MA==&mid=2247483737&idx=1&sn=a4be0603ea9c8cce1dfc23a1f0628af0&chksm=c2357b4ef542f258f122485563e7f1cda5d5886ec9d6548ea0e9f5338be54a4fcd6a33de0443&token=2065518836&lang=zh_CN#rd