请教 V 友一个 SQL 问题

有如下数据：

| id | phoneNum | callType | created |
| --- | ----------- | -------- | ---------------- |
| 1 | 15000000000 | 1 | 2019-08-08 01:02 |
| 2 | 15000000000 | 1 | 2019-08-08 02:03 |
| 3 | 15000000000 | 2 | 2019-08-08 04:05 |
| 4 | 15000000000 | 1 | 2019-08-08 06:07 |

处理成：

| phoneNum | callType | created |
| -------------- | -------- | ---------------- |
| 15000000000 | 1 | 2019-08-08 06:07 |
| 15000000000 | 2 | 2019-08-08 04:05 |
| 15000000000(2) | 1 | 2019-08-08 02:03 |

---

将数据读出来后处理，遍历后处理成想要的数据没啥问题。
但想试试用`SQL`来处理，没成功。
想请教一下 V 友有啥好的思路吗？

---
用的是`sqlite`，有`sqlite`语法最好 :)。先谢过~

Supplement 1 · Aug 9, 2019

解决了。附上结果，也许哪天也有朋友需要做通讯录数据的处理，可以参考一下。

select phoneNum, count(*) as cnt, callType, max(created) created
from (select t.*,
             sum(case when prev_phonenum = phoneNum and prev_type = callType and prev_created=date(created) then 0 else 1 end) over (order by id) as grp
      from (select t.*,
                   lag(callType) over (order by id) as prev_type,
                   lag(phonenum) over (order by id) as prev_phonenum,
                   lag(date(created)) over (order by id) as prev_created
            from t
           ) t
     ) t
group by phoneNum, callType, grp
order by id desc limit 80 offset 0

4 replies • 2019-08-09 10:11:12 +08:00

orangeChu

Aug 8, 2019

为啥`md`的`table`语法没生效？

Iamnotfish

Aug 8, 2019

'''sqlite

select group_concat(phoneNum), callType, created from table group by callType

'''
抛砖引玉一下，看看大佬怎么回答。PS : V2 的 MD 语法不支持 TABLE

orangeChu

Aug 8, 2019 via iPhone

@Iamnotfish 感谢回复，（我还没试您给的答案，将在明天尝试一下）最后自己搞定了。
分享一下结果。（可能没有在描述中说明场景不是那么好搞，场景是这样的，记录来电记录，每次来电插入记录到数据库。展示数据时，按倒序来显示。上下两条号码相同时，根据 callType 来决定是否展示新的一行数据；不同号码时，直接展示该数据。展示结果类似手机上的通话记录~）
```
select phonenum, count(*) as cnt, type, max(created)
from (select t.*,
row_number() over (partition by phonenum order by id) as seqnum,
row_number() over (partition by phonenum, type order by id) as seqnum_t
from t
) t
group by phonenum, type, (seqnum - seqnum_t);
```

orangeChu

Aug 9, 2019

@Iamnotfish 尝试了一下，得到的结果好像不是我想要的。
---
补充一下原来的题目：
data
| id | phoneNum | callType | created |
| --- | ----------- | -------- | ---------------- |
| 1 | 15000000000 | 1 | 2019-08-07 01:02 |
| 2 | 15000000000 | 1 | 2019-08-07 02:03 |
| 3 | 15000000000 | 2 | 2019-08-07 04:05 |
| 4 | 15000000000 | 1 | 2019-08-07 05:07 |
| 5 | 15000000000 | 2 | 2019-08-07 06:07 |
| 6 | 15000000000 | 1 | 2019-08-07 06:20 |
| 7 | 15000000000 | 1 | 2019-08-07 06:30 |
| 8 | 15000000001 | 1 | 2019-08-07 07:07 |
| 9 | 15000000000 | 1 | 2019-08-07 08:07 |
| 10 | 15000000000 | 1 | 2019-08-08 08:07 |

result
| phoneNum | callType | created | count |
| -------------- | -------- | ---------------- | ----- |
| 15000000000 | 1 | 2019-08-07 02:03 | 2 |
| 15000000000 | 2 | 2019-08-07 04:05 | 1 |
| 15000000000 | 1 | 2019-08-07 05:07 | 1 |
| 15000000000 | 2 | 2019-08-07 06:07 | 1 |
| 15000000000 | 1 | 2019-08-07 06:30 | 2 |
| 15000000001 | 1 | 2019-08-07 07:07 | 1 |
| 15000000000 | 1 | 2019-08-07 08:07 | 1 |
| 15000000000 | 1 | 2019-08-08 08:07 | 1 |