计算sqlite中的相邻记录

马哈茂德·亚当

我有下表

ID  location date
--  -------- -----
01  loc#1    10-06-2014
05  loc#1    11-06-2014
06  loc#2    13-06-2014
08  loc#2    14-06-2014
10  loc#2    15-06-2014
14  loc#1    16-06-2014
17  loc#1    17-06-2014
20  loc#1    18-06-2014

我需要的是获取每个位置以及在ID的顺序上提到该位置的相邻记录的数量

location count(location)
-------- --------------- 
loc#1    2 
loc#2    3 
loc#1    3

使用count + group by的问题在于，它对同一位置的所有记录进行计数，而不仅仅是相邻位置，并且输出唯一的一组位置

select location, count(location) from table group by location order by ID

location count(location)
-------- --------------- 
loc#1    5
loc#2    3

任何想法如何获得正确的查询？因为我不想以编程方式制作它，因为我正在使用iPhone上的数千条记录，这会产生性能问题

何塞·马尔加萨·洛佩斯（JoséMargaçaLopes）

select minAdjLoc.ID, max(minAdjLoc.location) 'Loc', count(distinct adjLocs.ID) 'Count' 
from test minAdjLoc              -- Minimum record in each adjacent group
inner join test adjLocs          -- All adjacent record, including self
    on adjLocs.location = minAdjLoc.location
    and adjLocs.ID >= minAdjLoc.ID
left join test intruder          -- Possible intruder with different location
    on intruder.location <> minAdjLoc.location
    and intruder.ID > minAdjLoc.ID
    and intruder.ID < adjLocs.ID
left join test lowerThanMin      -- Possible record lower than minAdjLoc
    on lowerThanMin.ID < minAdjLoc.ID
    and lowerThanMin.location <> minAdjLoc.location
left join test lowerIntruder
    on (lowerThanMin.ID is null or lowerThanMin.ID < lowerIntruder.ID)
    and lowerIntruder.ID < minAdjLoc.ID
    and lowerIntruder.location = minAdjLoc.location
    where intruder.ID is null    -- There can't be any record with a different location inside the group
    and lowerIntruder.ID is null -- Ensure minAdjLoc is in fact the record with minimum ID
group by minAdjLoc.ID            --The minimum ID of the adjacent group is unique
order by minAdjLoc.ID

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。