jasnije je .. isti princip .. mogao si da probas sam ..
Code:
mysql> create table t1 (datum int auto_increment primary key, broj int, znak char(1)) engine=myisam;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> insert into t1 (broj, znak) values
-> (12, '+'),
-> (57, '-'),
-> (48, '-'),
-> (72, '-'),
-> (25, '-'),
-> (59, '+'),
-> (52, '+'),
-> (25, '-'),
-> (54, '+'),
-> (25, '-'),
-> (5, '+'),
-> (6, '+'),
-> (5, '+'),
-> (7, '+'),
-> (5, '+'),
-> (43, '-'),
-> (23, '+'),
-> (23, '-'),
-> (12, '+'),
-> (56, '-'),
-> (96, '+'),
-> (44, '-'),
-> (43, '+');
Query OK, 23 rows affected (0.00 sec)
Records: 23 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+-------+------+------+
| datum | broj | znak |
+-------+------+------+
| 1 | 12 | + |
| 2 | 57 | - |
| 3 | 48 | - |
| 4 | 72 | - |
| 5 | 25 | - |
| 6 | 59 | + |
| 7 | 52 | + |
| 8 | 25 | - |
| 9 | 54 | + |
| 10 | 25 | - |
| 11 | 5 | + |
| 12 | 6 | + |
| 13 | 5 | + |
| 14 | 7 | + |
| 15 | 5 | + |
| 16 | 43 | - |
| 17 | 23 | + |
| 18 | 23 | - |
| 19 | 12 | + |
| 20 | 56 | - |
| 21 | 96 | + |
| 22 | 44 | - |
| 23 | 43 | + |
+-------+------+------+
23 rows in set (0.00 sec)
jel ovo trazis:
Code:
mysql> set @zzz=NULL; set @zz=NULL; set @z=NULL; set @nnn=NULL; set @nn=NULL; set @n=NULL;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * , @zzz:=@zz a, @zz:=@z b, @z:=znak c, @nnn:=@nn d, @nn:=@n e, @n:=broj f from t1 order by broj,datum,znak;
+-------+------+------+------+------+------+------+------+------+
| datum | broj | znak | a | b | c | d | e | f |
+-------+------+------+------+------+------+------+------+------+
| 11 | 5 | + | NULL | NULL | + | NULL | NULL | 5 |
| 13 | 5 | + | NULL | + | + | NULL | 5 | 5 |
| 15 | 5 | + | + | + | + | 5 | 5 | 5 |
| 12 | 6 | + | + | + | + | 5 | 5 | 6 |
| 14 | 7 | + | + | + | + | 5 | 6 | 7 |
| 1 | 12 | + | + | + | + | 6 | 7 | 12 |
| 19 | 12 | + | + | + | + | 7 | 12 | 12 |
| 17 | 23 | + | + | + | + | 12 | 12 | 23 |
| 18 | 23 | - | + | + | - | 12 | 23 | 23 |
| 5 | 25 | - | + | - | - | 23 | 23 | 25 |
| 8 | 25 | - | - | - | - | 23 | 25 | 25 |
| 10 | 25 | - | - | - | - | 25 | 25 | 25 |
| 16 | 43 | - | - | - | - | 25 | 25 | 43 |
| 23 | 43 | + | - | - | + | 25 | 43 | 43 |
| 22 | 44 | - | - | + | - | 43 | 43 | 44 |
| 3 | 48 | - | + | - | - | 43 | 44 | 48 |
| 7 | 52 | + | - | - | + | 44 | 48 | 52 |
| 9 | 54 | + | - | + | + | 48 | 52 | 54 |
| 20 | 56 | - | + | + | - | 52 | 54 | 56 |
| 2 | 57 | - | + | - | - | 54 | 56 | 57 |
| 6 | 59 | + | - | - | + | 56 | 57 | 59 |
| 4 | 72 | - | - | + | - | 57 | 59 | 72 |
| 21 | 96 | + | + | - | + | 59 | 72 | 96 |
+-------+------+------+------+------+------+------+------+------+
tj ... tri uzastopna broja koja imaju isti znak dakle:
Code:
mysql> set @zzz=NULL; set @zz=NULL; set @z=NULL; set @nnn=NULL; set @nn=NULL; set @n=NULL;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select datum, broj, znak from
-> ( select * , @zzz:=@zz a, @zz:=@z b, @z:=znak c, @nnn:=@nn d, @nn:=@n e, @n:=broj f from t1 order by broj, datum, znak) t
-> where
-> t.a=t.b and t.a=t.c -- tri zadnja znaka ista
-> and
-> t.d=t.e and t.d=t.f -- tri zadnja broja ista
-> ;
+-------+------+------+
| datum | broj | znak |
+-------+------+------+
| 15 | 5 | + |
| 10 | 25 | - |
+-------+------+------+
2 rows in set (0.00 sec)
jel sad to sto si trazio?