MySql GROUP BY 和 ORDER BY 重复数据删除

Question

我有一个包含下表的数据库:

trips
stores
trips_stores (this table records which stores are visited on each trip)

每次访问都有一个访问状态:

NULL - Not visited
0 - Visit pending
1 - Visited
2 - Skipped

我只想为每个商店返回一条记录(即使每个商店可能有多次访问)。但是，我只想返回最低访问状态编号:NULL、0、1 或 2。

例如，我当前的查询返回以下内容:

+---------+--------------------------------------+------------+------------+
| storeID |              storeCity               | visitStatus|  tripDate  |
+---------+--------------------------------------+------------+------------+
|     634 | BLACKBURN                            |          1 | 05/17/2014 |
|     634 | BLACKBURN                            |          0 | 07/30/2014 |
|     634 | BLACKBURN                            |          2 | 07/30/2014 |
|     636 | WARRINGTON                           |       NULL | 07/30/2014 |
+---------+--------------------------------------+------------+------------+

但我需要它返回:

+---------+--------------------------------------+------------+------------+
| storeID |              storeCity               | visitStatus|  tripDate  |
+---------+--------------------------------------+------------+------------+
|     634 | BLACKBURN                            |          0 | 07/30/2014 |
|     636 | WARRINGTON                           |       NULL | 07/30/2014 |
+---------+--------------------------------------+------------+------------+

我尝试了 GROUP BY 和 ORDER BY 的各种组合，但我无法弄清楚。非常感谢任何帮助

我当前的查询

SELECT 
    stores.id AS storeID, 
    stores.city AS storeCity,
    trips_stores.status AS visitStatus,
    trips.trip_date AS tripDate
FROM stores LEFT JOIN trips_stores on stores.id = trips_stores.store_id
LEFT JOIN trips ON trips_stores.trip_id = trips.id
WHERE stores.map_display = 1
ORDER BY storeID ASC

Answer 1

对于此或更复杂的查询，您需要group by。通过 group by，您可以使用 substring_index()/group_concat() 技巧:

SELECT s.id AS storeID, s.city AS storeCity,
       (case when sum(ts.status is null) > 0 then NULL
             else min(ts.status)
        end) AS visitStatus,
       substring_index(group_concat(t.trip_date order by coalesce(ts.status, -1)), ',', 1
                      ) AS tripDate
FROM stores s LEFT JOIN
     trips_stores ts
     on s.id = ts.store_id LEFT JOIN
     trips t
     ON ts.trip_id = t.id
WHERE s.map_display = 1
GROUP BY storeid, storecity
ORDER BY storeID ASC;

正确处理NULL值需要一些逻辑。因为 NULL 是最小值 - 即使存在其他值 - 您需要在 min() 中显式检查该值。