android - Sqlite - 如果有相同的号码，我如何像通话记录一样查询继续获取单个记录，仅连续记录计数？

Question

UniqueID | MobileNumber | createDate
-----------+---------+-----+------------+-----------
U_23121  | 987654        | 2013-02-05 
U_23124  | 987654        | 2013-02-02 
U_23122  | 845263        | 2013-01-18 
U_23128  | 654789        | 2013-01-16 
U_23123  | 735689        | 2013-01-12 
U_23128  | 654789        | 2013-01-11 
U_23128  | 654789        | 2013-01-10 
U_23126  | 987654        | 2013-01-09 
U_23125  | 845263        | 2013-01-07 
U_23126  | 845263        | 2013-01-06 
U_23125  | 987654        | 2013-01-05 

我想记录喜欢用手机号码过滤，如果超过一个继续根据 createdDate 获取最新的，并得到像这样的计数

        UniqueID | Mobile_Number | createDate   | count
        -----------+---------+-----+------------+-----------
        U_23121  | 987654        | 2013-02-05   | 2
        U_23122  | 845263        | 2013-01-18   | 1
        U_23128  | 654789        | 2013-01-16   | 1
        U_23123  | 735689        | 2013-01-12   | 1
        U_23128  | 654789        | 2013-01-11   | 2
        U_23126  | 987654        | 2013-01-09   | 1
        U_23125  | 845263        | 2013-01-07   | 2
        U_23125  | 987654        | 2013-01-05   | 1

我将从以下查询中获取所需的记录但未获取计数

SELECT te.*
FROM tableName as te
WHERE te.Mobile_Number <> (select Mobile_Number 
                           from tableName
                           where createDate > te.createDate
                           limit 1
                          )
ORDER BY te.createDate DESC

Answer 1

这是一个缺口和孤岛问题。一种解决方案是为每一行分配一个“grp”，然后按该组进行聚合。

您可以通过计算每行中与手机号码不同的手机号码的数量来分配 grp，直到该行。这是相邻手机号码的常数值。

结果查询:

SELECT MAX(UniqueId), MobileNumber,
       MAX(createDate), COUNT(*)
FROM (SELECT te.*,
             (SELECT COUNT(*)
              FROM tableName te2
              WHERE te2.createDate < te.createDate AND
                    te2.MobileNumber <> te.MobileNumber
             ) as grp
      FROM tableName te
     ) te
GROUP BY MobileNumber, grp;
ORDER BY MIN(tcreateDate) DESC