php - mysql中的行编号

Question

我想对 mysql 中选定的行进行编号。我的 session table 结构:

id | date
4  | 2014-09-29 
5  | 2014-10-06
6  | 2014-10-13 
7  | 2014-10-20 
8  | 2014-10-27
9  | 2014-11-03
10 | 2014-11-10

所以我只想选择从今天开始较大的日期行，结果应该是这样的:

id | date        | meeting number
8  | 2014-10-27  | 5
9  | 2014-11-03  | 6
10 | 2014-11-10  | 7

Answer 1

您可以使用自连接按日期的顺序对行进行编号

select t1.id, t1.date, count(*) meeting_number
from mytable t1
join mytable t2 on t1.date >= t2.date
where t1.date > current_date()
group by t1.id

在同一日期召开多个 session 时使用变量的另一种方法

select id, date, meeting_number
from (
    select *, 
    (@meeting_number := @meeting_number + 1) meeting_number
    from mytable cross join (select @meeting_number := 0) t1
    order by date
) t1 where date > current_date()