问题描述

最近，笔者遇到了这样一个问题，举例说明一下。由于某些原因，真实的数据表被替换为虚构的水果相关表，并且虚构数据之间的对应关系与实际并不相符！

小A是个农场主，他遍布全国的果园里种植了大量不同的水果。对于每种水果，都有特定的适用的杀虫剂。对于如下三个表：

水果表（fruits）

id name type color area pesticide_id …
1 红富士 苹果 红色 烟台 3 …
2 华蕉 香蕉 黄色 海南 2 …
3 小香芒 芒果 黄色 海南 2 …
4 金枕榴莲 榴莲 黄色 海南 1 …
… … … … … … …

杀虫剂订购清单表（orders）

id pesticide_id order_num order_time …
1 2 26 2023-10-15 12:35:14 …
2 1 32 2023-10-15 18:12:47 …
3 2 17 2023-10-16 09:25:39 …
… … … … …

杀虫剂表（pesticides）

id name …
1 辛硫磷 …
2 灭扑威 …
3 除虫脲 …
… … …

现在，他需要根据水果的名称、种类、颜色、产地等进行筛选，查找出指定时间段内某几种杀虫剂的每一天订购总数。例如，“查找2023年10月5号到2023年10月16号内，所有适用于颜色为黄色、产地为海南的水果的杀虫剂的每日订购数量”。

初始方案

遇到这个问题以后，因为需要针对fruits表中的字段做筛选，但查找内容却在orders表中，所以我首先想到的是用JOIN语句。以上节中的具体问题为例，第一版SQL语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT sum(orders.order_num)
FROM orders
JOIN fruits ON orders.pesticide_id = fruits.pesticide_id
WHERE fruits.color = '黄色' AND fruits.area = '海南'
      AND orders.order_time >= '2023-10-05 00:00:00'
      AND orders.order_time <= '2023-10-06 00:00:00'

...

XXXXXXXXXXX
      AND orders.order_time >= '2023-10-15 00:00:00'
      AND orders.order_time <= '2023-10-16 00:00:00'

对于表中的示例数据来说，在2023年10月15日，我们期望的结果应该是26+32=58。而上述SQL执行的结果却是84。

问题分析

对比正确答案58，得到的结果84多出了26，而这刚好是当天杀虫剂2号的订购量。简单分析后，笔者发现了问题所在：JOIN操作会在联结的两表对应的字段上形成笛卡尔积，如果A表与B表在c_id上联结，且同一个c_id在A表中出现了n次，在B表中出现了m次，那么JOIN形成的临时表里那个c_id的数据行就会有m*n行。对于案例表来说，形成的临时表如下：

id name type color area pesticide_id order_num order_time …
… … … … … … … … …
2 华蕉 香蕉 黄色 海南 2 26 2023-10-15 12:35:14 …
3 小香芒 芒果 黄色 海南 2 26 2023-10-15 12:35:14 …
4 金枕榴莲 榴莲 黄色 海南 1 32 2023-10-15 18:12:47
… … … … … … … … …

所以，得到的结果会比正确答案多若干倍的重复行的值。

解决方案

既然明确了问题，那么解决思路也就很直接。去掉笛卡尔积导致的重复行就可以了。

方案一：JOIN+子查询

如果保留JOIN操作，那么可以如下改进：

1
2
3
4
5
6
7
8
9
SELECT sum(num)
FROM
	(SELECT orders.id, orders.order_num AS num
	FROM orders
	JOIN fruits ON orders.pesticide_id = fruits.pesticide_id
	WHERE fruits.color = '黄色' AND fruits.area = '海南'
		AND orders.order_time >= '2023-10-05 00:00:00'
		AND orders.order_time <= '2023-10-06 00:00:00'
	GROUP BY orders.id) AS temp

但这样依然形成了笛卡尔积，在数据量大的时候容易数据爆炸。因此，笔者不推荐这种方法。

方案二：直接子查询

1
2
3
4
5
6
7
SELECT sum(orders.order_num)
FROM orders, (SELECT DISTINCT fruits.pesticide_id AS pid
	FROM fruits
	WHERE fruits.color = '黄色' AND fruits.area = '海南') AS temp
WHERE orders.pesticide_id = temp.pid
	AND orders.order_time >= '2023-10-05 00:00:00'
	AND orders.order_time <= '2023-10-06 00:00:00'

这种方法避免了笛卡尔积，因此更好一些。