值更改时，MySQL递增用户变量 [英] MySQL increment user variable when value changes

问题描述

我有一个表，该表由例如每行五行的组组成.每个组中的每一行都具有该组唯一的date值.

I have a table consisting of groups of, for example, five rows each. Each row in each group possesses a date value unique to that group.

我要在查询中执行的操作是遍历表，并在此date值更改时增加用户变量(@count).也就是说，@ count应该等于组数，而不是行数.

What I want to do in my query, is go through the table, and increment a user variable (@count) when this date value changes. That's to say, @count should equal the group count, rather than the row count.

我当前的查询如下所示，以防万一:

My current query looks like this, in case you're wondering:

SELECT @row := @row +1 AS rownum, date
FROM ( SELECT @row := 0 ) r, stats

非常感谢.

推荐答案

这样的事情怎么办?

SELECT 
    (CASE WHEN @date <> date THEN @row := @row +1 ELSE @row END) AS rownum, 
    @date:= date, 
    date
FROM ( SELECT @row := 0, @date := NOW() ) r, stats

这篇关于值更改时，MySQL递增用户变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！