如何限制mysql中变量的增量 [英] How to limit the increment of variable in mysql

查看：73
本文介绍了如何限制mysql中变量的增量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  + ---- + -------- +  -   - -------- + ------------ + 
 | id | idName | fldName | fld_Date | 
 + ---- + -------- + --------- + ------------ + 
 | 1 | 1 |马龙| 2013-06-03 | 
 | 2 | 1 |马龙| 2013-06-05 | 
 | 3 | 1 |马龙| 2013-06-07 | 
 | 4 | 1 |马龙| 2013-06-08 | 
 | 5 | 1 |马龙| 2013-06-11 | 
 | 6 | 2 |黎明| 2013-06-03 | 
 | 7 | 2 |黎明| 2013-06-06 | 
 | 8 | 2 |黎明| 2013-06-08 | 
 | 9 | 2 |黎明| 2013-06-11 | 
 | 10 | 2 |黎明| 2013-06-15 | 
 | 11 | 3 |珍妮| 2013-06-15 | 
 | 12 | 3 |珍妮| 2013-06-19 | 
 | 13 | 3 |珍妮| 2013-06-14 | 
 | 14 | 3 |珍妮| 2013-06-21 | 
 + ---- + -------- + --------- + ------------ + 
  
 
 $ b 
输出应该是这样的
  + ---- + -------- + --------- + ---- + ------------ + ------ ------ + 
 | id | idName | fldName |我| fld_Date | next_date | 
 + ---- + -------- + --------- + ---- + ------------ + --- --------- + 
 | 1 | 1 |马龙| 1 | 2013-06-03 | 2013-06-05 | 
 | 2 | 1 |马龙| 2 | 2013-06-05 | 2013-06-07 | 
 | 3 | 1 |马龙| 3 | 2013-06-07 | 2013-06-08 | 
 | 4 | 1 |马龙| 4 | 2013-06-08 | 2013-06-11 | 
 | 5 | 1 |马龙| 5 | 2013-06-11 | 0 | 
 | 6 | 2 |黎明| 1 | 2013-06-03 | 2013-06-06 | 
 | 7 | 2 |黎明| 2 | 2013-06-06 | 2013-06-08 | 
 | 8 | 2 |黎明| 3 | 2013-06-08 | 2013-06-11 | 
 | 9 | 2 |黎明| 4 | 2013-06-11 | 2013-06-15 | 
 | 10 | 2 |黎明| 5 | 2013-06-15 | 0 | 
 | 11 | 3 |珍妮| 1 | 2013-06-15 | 2013-06-19 | 
 | 12 | 3 |珍妮| 2 | 2013-06-19 | 2013-06-14 | 
 | 13 | 3 |珍妮| 3 | 2013-06-14 | 2013-06-21 | 
 | 14 | 3 |珍妮| 4 | 2013-06-21 | 0 | 
 + ---- + -------- + --------- + ---- + ------------ + --- --------- + 
  
以下是我的代码... 
  SELECT id，idName，fldName，@i：= @i + 1 i，fld_Date，next_date 
 FROM（SELECT id，idName ，fldName，@d next_date，@d：= fld_Date fld_Date 
 FROM x_table，
（SELECT @d：= 0）d ORDER BY id DESC）q，
（SELECT @i：= 0）n ORDER BY id; 
  
我很难根据不同的 idName  ... 
您可以帮助我解决这些问题.... 
解决方案
试试这个：
 $ b $ pre $  SELECT id，idname，fldname，IF（@idname =（@ idname：= idname），@id： = @ id + 1，@id：= 1）i，fld_Date，next_Date 
 FROM（SELECT a.id，a.idName，a.fldName，a.fld_Date，IFNULL（b.fld_Date，0）next_Date 
 FROM x_table a 
 LEFT JOIN x_table b ON a.idname = b.idname AND a.fld_Date< b.fld_Date 
 GROUP BY a.id）A，（SELECT @id：= 0 ，@idname：= 0）B

检查 FIDDLE



OUTPUT 

  | ID | IDNAME | FLDNAME |我| FLD_DATE | NEXT_DATE | 
 ---------------------------------------------- -------------------------- 
 | 1 | 1 |马龙| 1 | June，03 2013 00：00：00 + 0000 | 2013-06-05 | 
 | 2 | 1 |马龙| 2 | 2013年6月5日00：00：00 + 0000 | 2013-06-07 | 
 | 3 | 1 |马龙| 3 | June，07 2013 00：00：00 + 0000 | 2013-06-08 | 
 | 4 | 1 |马龙| 4 | 2013年6月，00：00：00 + 0000 | 2013-06-11 | 
 | 5 | 1 |马龙| 5 | 2013年6月11日00：00：00 + 0000 | 0 | 
 | 6 | 2 |黎明| 1 | June，03 2013 00：00：00 + 0000 | 2013-06-06 | 
 | 7 | 2 |黎明| 2 | June，06 2013 00：00：00 + 0000 | 2013-06-08 | 
 | 8 | 2 |黎明| 3 | 2013年6月，00：00：00 + 0000 | 2013-06-11 | 
 | 9 | 2 |黎明| 4 | 2013年6月11日00：00：00 + 0000 | 2013-06-15 | 
 | 10 | 2 |黎明| 5 | 2013年6月15日00：00：00 + 0000 | 0 | 
 | 11 | 3 |珍妮| 1 | 2013年6月15日00：00：00 + 0000 | 2013-06-19 | 
 | 12 | 3 |珍妮| 2 | 2013年6月19日00：00：00 + 0000 | 2013-06-21 | 
 | 13 | 3 |珍妮| 3 | 2013年6月14日00：00：00 + 0000 | 2013-06-15 | 
 | 14 | 3 |珍妮| 4 | 2013年6月21日00：00：00 + 0000 | 0 |


here's my table....

+----+--------+---------+------------+
| id | idName | fldName | fld_Date   |
+----+--------+---------+------------+
|  1 |      1 | Marlon  | 2013-06-03 |
|  2 |      1 | Marlon  | 2013-06-05 |
|  3 |      1 | Marlon  | 2013-06-07 |
|  4 |      1 | Marlon  | 2013-06-08 |
|  5 |      1 | Marlon  | 2013-06-11 |
|  6 |      2 | Dawn    | 2013-06-03 |
|  7 |      2 | Dawn    | 2013-06-06 |
|  8 |      2 | Dawn    | 2013-06-08 |
|  9 |      2 | Dawn    | 2013-06-11 |
| 10 |      2 | Dawn    | 2013-06-15 |
| 11 |      3 | Jenny   | 2013-06-15 |
| 12 |      3 | Jenny   | 2013-06-19 |
| 13 |      3 | Jenny   | 2013-06-14 |
| 14 |      3 | Jenny   | 2013-06-21 |
+----+--------+---------+------------+

The output should be like this

+----+--------+---------+----+------------+------------+
| id | idName | fldName | i  | fld_Date   | next_date  |
+----+--------+---------+----+------------+------------+
|  1 |      1 | Marlon  |  1 | 2013-06-03 | 2013-06-05 |
|  2 |      1 | Marlon  |  2 | 2013-06-05 | 2013-06-07 |
|  3 |      1 | Marlon  |  3 | 2013-06-07 | 2013-06-08 |
|  4 |      1 | Marlon  |  4 | 2013-06-08 | 2013-06-11 |
|  5 |      1 | Marlon  |  5 | 2013-06-11 | 0          |
|  6 |      2 | Dawn    |  1 | 2013-06-03 | 2013-06-06 |
|  7 |      2 | Dawn    |  2 | 2013-06-06 | 2013-06-08 |
|  8 |      2 | Dawn    |  3 | 2013-06-08 | 2013-06-11 |
|  9 |      2 | Dawn    |  4 | 2013-06-11 | 2013-06-15 |
| 10 |      2 | Dawn    |  5 | 2013-06-15 | 0          |
| 11 |      3 | Jenny   |  1 | 2013-06-15 | 2013-06-19 |
| 12 |      3 | Jenny   |  2 | 2013-06-19 | 2013-06-14 |
| 13 |      3 | Jenny   |  3 | 2013-06-14 | 2013-06-21 |
| 14 |      3 | Jenny   |  4 | 2013-06-21 | 0          |
+----+--------+---------+----+------------+------------+

and here's my code...

SELECT id,idName,fldName, @i := @i + 1 i, fld_Date, next_date 
FROM ( SELECT id,idName,fldName, @d next_date, @d := fld_Date fld_Date 
       FROM x_table, 
      (SELECT @d := 0) d ORDER BY id DESC) q, 
      (SELECT @i := 0) n ORDER BY id;

I've got difficulty in limiting the increment based on the number of distinct idName... Can you help me with these....

解决方案

Try this: 

SELECT id, idname, fldname, IF(@idname=(@idname:=idname), @id:=@id+1, @id:=1) i, fld_Date, next_Date
FROM (SELECT a.id, a.idName, a.fldName, a.fld_Date, IFNULL(b.fld_Date, 0) next_Date
        FROM x_table a 
        LEFT JOIN x_table b ON a.idname = b.idname AND a.fld_Date < b.fld_Date
        GROUP BY a.id) A, (SELECT @id:=0, @idname:=0) B

Check the query on FIDDLE

OUTPUT

| ID | IDNAME | FLDNAME | I |                    FLD_DATE |  NEXT_DATE |
------------------------------------------------------------------------
|  1 |      1 |  Marlon | 1 | June, 03 2013 00:00:00+0000 | 2013-06-05 |
|  2 |      1 |  Marlon | 2 | June, 05 2013 00:00:00+0000 | 2013-06-07 |
|  3 |      1 |  Marlon | 3 | June, 07 2013 00:00:00+0000 | 2013-06-08 |
|  4 |      1 |  Marlon | 4 | June, 08 2013 00:00:00+0000 | 2013-06-11 |
|  5 |      1 |  Marlon | 5 | June, 11 2013 00:00:00+0000 |          0 |
|  6 |      2 |    Dawn | 1 | June, 03 2013 00:00:00+0000 | 2013-06-06 |
|  7 |      2 |    Dawn | 2 | June, 06 2013 00:00:00+0000 | 2013-06-08 |
|  8 |      2 |    Dawn | 3 | June, 08 2013 00:00:00+0000 | 2013-06-11 |
|  9 |      2 |    Dawn | 4 | June, 11 2013 00:00:00+0000 | 2013-06-15 |
| 10 |      2 |    Dawn | 5 | June, 15 2013 00:00:00+0000 |          0 |
| 11 |      3 |   Jenny | 1 | June, 15 2013 00:00:00+0000 | 2013-06-19 |
| 12 |      3 |   Jenny | 2 | June, 19 2013 00:00:00+0000 | 2013-06-21 |
| 13 |      3 |   Jenny | 3 | June, 14 2013 00:00:00+0000 | 2013-06-15 |
| 14 |      3 |   Jenny | 4 | June, 21 2013 00:00:00+0000 |          0 |

这篇关于如何限制mysql中变量的增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆