MySQL关联表过滤数据异常：为何经纬度条件未生效？

需求是从Insurance表中筛选满足以下两个条件的记录，最终求和符合条件的tiv_2016字段值：

• tiv_2015值与其他记录重复
• 经纬度（lat、lon）组合唯一
  预期结果为45，但现有查询得到75，错误选中了pid=3的记录。

原Insurance表结构及数据：

Input: 
Insurance table:
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
+-----+----------+----------+-----+-----+

错误的SQL查询语句：

WITH tb1 AS(
SELECT DISTINCT a.pid,a.tiv_2016
FROM Insurance a
JOIN Insurance b
ON a.pid != b.pid AND a.tiv_2015 = b.tiv_2015
JOIN Insurance c
ON a.pid != c.pid AND (a.lat, a.lon) != (c.lat, c.lon)
)
SELECT SUM(tiv_2016) AS tiv_2016
FROM tb1

条件失效的原因 #

你写的JOIN Insurance c ON a.pid != c.pid AND (a.lat, a.lon) != (c.lat, c.lon)逻辑是只要存在任意一条其他记录和当前a的经纬度不同，就保留a记录，但这和需求中的“经纬度组合唯一”完全不符。

以pid=3的记录为例：它的经纬度是(20,20)，虽然和pid=2的经纬度重复，但存在pid=1或pid=4的记录满足a.pid != c.pid且经纬度不同，所以这条记录会被JOIN操作保留下来，进而被计入求和结果。

而需求的第二个条件是该经纬度组合在表中没有其他重复的记录，也就是需要确保不存在任何一条其他记录和a的经纬度相同，而不是存在不同的。

正确的查询写法 #

可以通过窗口函数统计每个tiv_2015的出现次数，以及每个(lat,lon)组合的出现次数，再筛选符合条件的记录：

WITH stats AS (
    SELECT 
        pid,
        tiv_2016,
        COUNT(*) OVER (PARTITION BY tiv_2015) AS tiv2015_count,
        COUNT(*) OVER (PARTITION BY lat, lon) AS latlon_count
    FROM Insurance
)
SELECT SUM(tiv_2016) AS tiv_2016
FROM stats
WHERE tiv2015_count > 1  -- tiv_2015存在重复
  AND latlon_count = 1   -- 经纬度组合唯一

这个查询会正确筛选出pid=1和pid=4的记录，求和得到预期结果45。

内容的提问来源于stack exchange，提问作者mmmmmm