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

我有一个包含以下表格的数据库：

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

每次访问都有一个visitStatus：

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

我只想为每个商店返回一条记录（即使每个商店可能有多次访问）。但是，我只想返回最小的visitStatus数字：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

最佳答案

您需要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()中明确检查。