Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE `finals` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(10) DEFAULT NULL,
- `result` char(4) DEFAULT NULL,
- PRIMARY KEY (`id`)
- )
- mysql> select * from finals;
- +----+------+--------+
- | id | name | result |
- +----+------+--------+
- | 1 | John | Pass |
- | 2 | John | Fail |
- | 3 | John | Pass |
- | 4 | Kyle | Pass |
- | 5 | John | Pass |
- | 6 | Kyle | Pass |
- | 7 | Kyle | Pass |
- | 8 | Kyle | Fail |
- +----+------+--------+
- 8 rows in set (0.00 sec)
- mysql> SELECT name, GROUP_CONCAT(result ORDER BY id) as result_str FROM finals GROUP BY name having result_str LIKE '%Pass,Pass,Pass%';
- +------+---------------------+
- | name | result_str |
- +------+---------------------+
- | Kyle | Pass,Pass,Pass,Fail |
- +------+---------------------+
- 1 row in set (0.00 sec)
- SET @x = 0;
- SET @name = '';
- SET @result = '';
- SELECT name,consecutive FROM
- (SELECT
- name,
- (@nametag:=MD5(CONCAT(name,':',result))),
- (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
- (@name:=@nametag) inc
- FROM finals ORDER BY name,id) A
- WHERE consecutive >= 3;
- mysql> SELECT name,consecutive FROM
- -> (SELECT
- -> name,
- -> (@nametag:=MD5(CONCAT(name,':',result))),
- -> (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
- -> (@name:=@nametag) inc
- -> FROM finals ORDER BY name,id) A
- -> WHERE consecutive >= 3;
- +------+-------------+
- | name | consecutive |
- +------+-------------+
- | Kyle | 3 |
- +------+-------------+
- 1 row in set (0.02 sec)
- mysql>
- mysql> SELECT
- -> name,
- -> (@nametag:=MD5(CONCAT(name,':',result))),
- -> (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
- -> (@name:=@nametag) inc
- -> FROM finals ORDER BY name,id;
- +------+------------------------------------------+-------------+----------------------------------+
- | name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc |
- +------+------------------------------------------+-------------+----------------------------------+
- | John | 84cc30b986fe149dfb765dd09fad8a60 | 1 | 84cc30b986fe149dfb765dd09fad8a60 |
- | John | 534b3d163a04b74a72c6dbe68db1c01e | 1 | 534b3d163a04b74a72c6dbe68db1c01e |
- | John | 84cc30b986fe149dfb765dd09fad8a60 | 1 | 84cc30b986fe149dfb765dd09fad8a60 |
- | John | 84cc30b986fe149dfb765dd09fad8a60 | 2 | 84cc30b986fe149dfb765dd09fad8a60 |
- | Kyle | 30fac0873cf25ad17b38bc37bda4b850 | 1 | 30fac0873cf25ad17b38bc37bda4b850 |
- | Kyle | 30fac0873cf25ad17b38bc37bda4b850 | 2 | 30fac0873cf25ad17b38bc37bda4b850 |
- | Kyle | 30fac0873cf25ad17b38bc37bda4b850 | 3 | 30fac0873cf25ad17b38bc37bda4b850 |
- | Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0 | 1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
- +------+------------------------------------------+-------------+----------------------------------+
- 8 rows in set (0.00 sec)
- mysql>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement