sql - 计算sqlite中的相邻记录数

Question

我有下表

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 上处理数千条记录，这会产生性能问题

Answer 1

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