SQL - SQLite的计数连续编号
问题描述:
我有下列数据的表:SQL - SQLite的计数连续编号
id | numbers | date
----------------------------------
1 | -1-4-6- | 2009-10-26 15:30:20
2 | -1-4-7- | 2009-10-26 16:45:10
3 | -4-5-8- | 2009-10-27 11:21:34
4 | -2-6-7- | 2009-10-27 13:12:56
5 | -1-3-4- | 2009-10-28 14:22:14
6 | -1-2-4- | 2009-10-29 20:28:16
. . ....... . ...................
在这个例子表我用一个like
查询统计的数字,例如:
select count(*) from table where numbers like '%-4-%'
Result: 5
现在,如何我可以计算(使用像)一个数字连续出现多少次(在这种情况下是数字4)? 我的意思是:4号连续出现在ID 1,2,3和5,6,所以我想有结果的查询:2
答
这应该这样做。
create table "table" (id int, numbers text);
insert into "table" values (1, '-1-4-6-');
insert into "table" values (2, '-1-4-7-');
insert into "table" values (3, '-4-5-8-');
insert into "table" values (4, '-2-6-7-');
insert into "table" values (5, '-1-3-4-');
insert into "table" values (6, '-1-2-4-');
SELECT count(*)
FROM (
SELECT "table".*, temp1.id, temp2.id
FROM "table"
INNER JOIN "table" temp1
ON "table".id = temp1.id+1
LEFT JOIN (
SELECT id FROM "table" WHERE numbers LIKE '%-4-%'
) temp2 ON temp1.id+1 = temp2.id+2
WHERE "table".numbers LIKE '%-4-%'
AND "temp1".numbers LIKE '%-4-%'
AND temp2.id IS NULL
) consecutive_groups_gt_1
[编辑:添加测试数据和纠正报价]
[编辑:更改的查询只只能算哪里有行组至少有2名成员]
你对于编号为1的情况,还希望命中id = 1,2,5,6?它是否必须作为单独的“命中”返回1,2或者1,2,5,6是否足够? – jcollum 2009-10-30 04:52:21