在stackoverflow上看到一篇帖子:
I need help with this one, i have no idea how to make it.Here is my data in MySql
----------------------
| id | color |
----------------------
| 1 | green |
| 2 | red |
| 3 | red |
| 4 | black |
| 5 | red |
| 6 | black |
| 7 | black |
----------------------How to count the last data in color ? but not in all rows, I want to count only how much time is last data one after another. In this case result shuld be:black is 2 times in a rowor
----------------------
| id | color |
----------------------
| 1 | green |
| 2 | red |
| 3 | red |
| 4 | black |
| 5 | red |
| 6 | black |
| 7 | black |
| 9 | green |
| 10 | red |
| 11 | red |
| 12 | red |
----------------------and here will be:red is 3 times in a row最后有个大牛给解决了 用了下面的sqlselect max(amount) amount from (
select color,
@found := if(@found, true, @prev_color != color),
@prev_color := color,
@amount := @amount + (not @found) amount
from table, (
select @prev_color := (select color from table order by id desc limit 1),
@found := false,
@amount := 0) init
order by id desc
) s小弟表示这个sql写法从来没见过,求大神指导解释下非常感谢
I need help with this one, i have no idea how to make it.Here is my data in MySql
----------------------
| id | color |
----------------------
| 1 | green |
| 2 | red |
| 3 | red |
| 4 | black |
| 5 | red |
| 6 | black |
| 7 | black |
----------------------How to count the last data in color ? but not in all rows, I want to count only how much time is last data one after another. In this case result shuld be:black is 2 times in a rowor
----------------------
| id | color |
----------------------
| 1 | green |
| 2 | red |
| 3 | red |
| 4 | black |
| 5 | red |
| 6 | black |
| 7 | black |
| 9 | green |
| 10 | red |
| 11 | red |
| 12 | red |
----------------------and here will be:red is 3 times in a row最后有个大牛给解决了 用了下面的sqlselect max(amount) amount from (
select color,
@found := if(@found, true, @prev_color != color),
@prev_color := color,
@amount := @amount + (not @found) amount
from table, (
select @prev_color := (select color from table order by id desc limit 1),
@found := false,
@amount := 0) init
order by id desc
) s小弟表示这个sql写法从来没见过,求大神指导解释下非常感谢
select count(*) from tb
where id >(
select max(id) from tb
where color<>(
select color from tb
where id=(select max(id) from tb)
)
)