ID obj data
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3
10 a 4
11 b 4
12 c 4
13 a 5
14 b 5
15 c 5
数据表如下所示,其中ID为自动增量,obj为对象编码,data是可用数据。现要求所有不同对象obj为一组(本例中为a、b、c),要求其对应的data组成连续的数据串,如:ID(1,5,9)/obj(a,b,c)/data(1,2,3),又如ID(8,10,15)/obj(b,a,c)/data(3,4,5)。现求一SQL语句,列出所有可能的组合。
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3
10 a 4
11 b 4
12 c 4
13 a 5
14 b 5
15 c 5
数据表如下所示,其中ID为自动增量,obj为对象编码,data是可用数据。现要求所有不同对象obj为一组(本例中为a、b、c),要求其对应的data组成连续的数据串,如:ID(1,5,9)/obj(a,b,c)/data(1,2,3),又如ID(8,10,15)/obj(b,a,c)/data(3,4,5)。现求一SQL语句,列出所有可能的组合。
FROM 表名 a , 表名 b,表名 c where a.obj<>b.obj and b.obj<>c.obj and a.obj<>c.obj and a.data=b.data-1 and b.data=c.data-1;试试这个
from table1 a ,table1 b ,table1 c
where a.obj != b.obj and b.obj != c.obj and c.obj != a.obj
1、5、9是连续的?还是1、2、3?详细说明
谢谢各位,我想得到的结果如ymhtt所述.
3、2、1都算?
SELECT a.data,b.data,c.data
FROM (qt2 AS a LEFT JOIN qt2 AS b ON (a.data=b.data-1) AND (a.obj<>b.obj)) LEFT JOIN qt2 AS c ON (b.data=c.data-1) AND (c.obj<>b.obj) where b.data is not null and c.data is not null
union
SELECT a.data,b.data,c.data
FROM (qt2 AS a LEFT JOIN qt2 AS b ON (a.data=b.data+1) AND (a.obj<>b.obj)) LEFT JOIN qt2 AS c ON (b.data=c.data+1) AND (c.obj<>b.obj)
where b.data is not null and c.data is not null