mysql - 排除相同的值和特定的规则集查询

Question

自从我使用数据库以来已经有一段时间了，目前无法理解如何执行这些操作，所以如果有人可以帮助我解决这两个问题/查询，我将非常感激。

1. 我想显示至少位于 2 个不同区域的 ID(不包括仅位于同一区域的 ID)。

2. 我想显示首先位于 zone1、然后是 zone2、然后是 zone3 的 ID(需要在此处查看时间)。

原文:

ID    ZONE    TIME 
"01"  "ZO1"   "20190110_111326"
"01"  "ZO1"   "20190110_111355" 
"01"  "ZO1"   "20190110_111529"
"02"  "ZO3"   "20190110_112527"
"02"  "ZO3"   "20190110_112559"
"06"  "ZO1"   "20190114_082144" 
"06"  "ZO2"   "20190114_082153"
"06"  "ZO3"   "20190114_082210" 
"07"  "ZO3"   "20190114_131034"
"07"  "ZO2"   "20190114_131058"

1.

ID      ZONE    TIME
"06"    "ZO1"   "20190114_082144"
"06"    "ZO2"   "20190114_082153"
"06"    "ZO3"   "20190114_082210"
"07"    "ZO3"   "20190114_131034"
"07"    "ZO2"   "20190114_131058"

2.

ID      ZONE    TIME
"06"    "ZO1"   "20190114_082144"
"06"    "ZO2"   "20190114_082153"
"06"    "ZO3"   "20190114_082210"

Answer 1

第 1 个问题的答案

select * from table where id in 
(
select id from
(
   select id, zone
   from table 
   group by id, zone
)
group by id 
having count(1) > 1

)

第 2 个问题的答案

select t2.zone, t2.time, max(t2.id) as id from 
(select zone, min(time) minTime from table group by zone ) t1
join 
(select id, zone, time from table) t2 on t1.zone = t2.zone and t1.minTime = t2.time
group by t2.zone, t2.time

请注意，我们在第二个查询中获得最大 id 的原因是 b/c 可能同时有多个 id。