There is a need to group adjacent rows with the same values in certain fields.
For simplicity, from here such tables (on the left) need to get a (right):
| id | num | | num | count |
| 1 | 1 | | 1 | 2 |
| 2 | 1 | | 2 | 1 |
| 3 | 2 | | 1 | 3 |
| 4 | 1 | | 3 | 2 |
| 5 | 1 | | 4 | 1 |
| 6 | 1 |
| 7 | 3 |
| 8 | 3 |
| 9 | 4 |
That said, it is thought, pogolino — made: sqlfiddle.com/#!9/c9c30b/1/0
SELECT `num`, COUNT(`group`) AS `count`
IF (@prev = `num`, @group, @group := @group + 1) AS `group`,
@prev := `num` AS `prev`
JOIN (SELECT @prev := 0, @group := 0) AS `t1`
) AS `t2`
GROUP BY `group`;
But now there is a need to put this query in a view (VIEW), and this raises two problems:
1. In views you cannot use subqueries inside the FROM clause. You can work around this by creating an additional view for the subquery.
2. In views you cannot use variables.
And this is the problem. To think of solutions to this problem without using variables I failed. Therefore we appeal to the community, I would be grateful for any help.To abandon the idea it would not be desirable, because the actual query is very big (with lots of JOIN, UNION, etc). And work with it from code you need from one table, i.e., to have the opportunity to sort, filter, group, Limitati, etc.