本文介绍了如何限制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
检查
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中变量的增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！