MYSQL:每天将当前id值更改为序列id

Question

我想从下面的记录中显示id值：

id      name            date
24811   Foo             2011-04-26
24812   Bar             2011-04-26
24813   Bars            2011-04-27

变成这样的格式：

date + Q + 4 digit ID sequence start from first ~ last

结果是：

id              name            date
20110426Q0001   Foo             2011-04-26
20110426Q0002   Bar             2011-04-26
20110427Q0001   Bars            2011-04-27 

但我只是有个半边的疑问，怎么做四位数呢？

SELECT REPLACE( CONCAT( DATE( Inspection_datetime ) ,'Q',/*missing this query..*/),'-','') AS id,
       name,date
FROM mytable

试着回答如下：

row @g:= `date`         id              Model           Serial      Lot_no  Inspection_datetime
1   2011-01-31 07:44:21 20110131Q0001   KW-HDR81BTJD    176X0099    010A    2011-01-31 07:44:21
1   2011-01-31 08:31:31 20110131Q0001   KW-HDR81BTJD    176X0098    010A    2011-01-31 08:31:31
1   2011-01-31 08:34:57 20110131Q0001   KW-HDR81BTJD    176X0065    010A    2011-01-31 08:34:57
1   2011-01-31 08:46:43 20110131Q0001   KW-HDR81BTJD    176X0050    010A    2011-01-31 08:46:43

Answer 1

我不知道为什么哈立德的这两个查询在我的Mysql中都不起作用。即使我在sqlfiddle尝试过，它也能工作，但在我的数据库中不起作用。一周后，我为这个问题疯狂，我得到了答案：

我使用Khalid的第二个答案，然后尝试删除：

cross join (select @g=null, @r:= 0) t

然后在选择之前添加：

set @g=null,@r:= 0;

Answer 2

您可以通过使用用户定义的变量来实现。

select 
@r:= case when @g = date then @r +1 else 1 end rownum,
@g:= date,
REPLACE( CONCAT( 
  DATE(date) ,
  'Q'
  ,lpad(@r,4,'0')
  ),'-','') AS id,
name,
date
from mytable
cross join (select @g=null, @r:= 0) t
order by date

DEMO

如果你只关心日期部分，而不是时间部分，那么你可以这样做。

select 
@r:= case when @g = date(date) then @r +1 else 1 end rownum,
@g:= date(date),
REPLACE( CONCAT( 
  DATE(date) ,
  'Q'
  ,lpad(@r,4,'0')
  ),'-','') AS id,
name,
date
from mytable
cross join (select @g=null, @r:= 0) t
order by date

DEMO