如何从fld_Date的参考字段获取下一个日期字段？ [英] How to get the next date field from the reference field of fld_Date?
本文介绍了如何从fld_Date的参考字段获取下一个日期字段？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的查询...
+----+--------+---------+---+------------+------------+
| 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 | 2013-07-01 |
| 19 | 1 | Marlon | 6 | 2013-07-01 | 2013-07-07 |
| 20 | 1 | Marlon | 7 | 2013-07-07 | 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 |
| 13 | 3 | Jenny | 1 | 2013-06-14 | 2013-06-15 |
| 11 | 3 | Jenny | 2 | 2013-06-15 | 2013-06-19 |
| 12 | 3 | Jenny | 3 | 2013-06-19 | 2013-06-21 |
| 14 | 3 | Jenny | 4 | 2013-06-21 | 0 |
| 15 | 4 | Rhea | 1 | 2013-06-21 | 2013-06-22 |
| 16 | 4 | Rhea | 2 | 2013-06-22 | 2013-06-23 |
| 17 | 4 | Rhea | 3 | 2013-06-23 | 2013-06-24 |
| 18 | 4 | Rhea | 4 | 2013-06-24 | 0 |
| 22 | 5 | Chrisha | 1 | 2013-07-07 | 2013-09-07 | <
| 23 | 5 | Chrisha | 2 | 2013-07-08 | 2013-09-07 | <
| 24 | 5 | Chrisha | 3 | 2013-07-11 | 2013-09-07 | <
| 25 | 5 | Chrisha | 4 | 2013-07-16 | 2013-09-07 | <
| 26 | 5 | Chrisha | 5 | 2013-07-17 | 2013-09-07 | <
| 27 | 5 | Chrisha | 6 | 2013-07-22 | 2013-09-07 |
| 21 | 5 | Chrisha | 7 | 2013-09-07 | 0 |
+----+--------+---------+---+------------+------------+
这是我想要的输出....上面标有< ）
Here's the output I wanted.... (differences from above marked with <)
+----+--------+---------+---+------------+------------+
| 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 | 2013-07-01 |
| 19 | 1 | Marlon | 6 | 2013-07-01 | 2013-07-07 |
| 20 | 1 | Marlon | 7 | 2013-07-07 | 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 |
| 13 | 3 | Jenny | 1 | 2013-06-14 | 2013-06-15 |
| 11 | 3 | Jenny | 2 | 2013-06-15 | 2013-06-19 |
| 12 | 3 | Jenny | 3 | 2013-06-19 | 2013-06-21 |
| 14 | 3 | Jenny | 4 | 2013-06-21 | 0 |
| 15 | 4 | Rhea | 1 | 2013-06-21 | 2013-06-22 |
| 16 | 4 | Rhea | 2 | 2013-06-22 | 2013-06-23 |
| 17 | 4 | Rhea | 3 | 2013-06-23 | 2013-06-24 |
| 18 | 4 | Rhea | 4 | 2013-06-24 | 0 |
| 22 | 5 | Chrisha | 1 | 2013-07-07 | 2013-07-08 | <
| 23 | 5 | Chrisha | 2 | 2013-07-08 | 2013-07-11 | <
| 24 | 5 | Chrisha | 3 | 2013-07-11 | 2013-07-16 | <
| 25 | 5 | Chrisha | 4 | 2013-07-16 | 2013-07-17 | <
| 26 | 5 | Chrisha | 5 | 2013-07-17 | 2013-07-22 | <
| 27 | 5 | Chrisha | 6 | 2013-07-22 | 2013-09-07 |
| 21 | 5 | Chrisha | 7 | 2013-09-07 | 0 |
+----+--------+---------+---+------------+------------+
我的问题是每次向fld_Date插入日期是从下一行高级，next_date行受复制高级日期到下一行日期...有没有解决这个...
这是我的sql代码...
My problem is every time I insert a date to fld_Date that is advanced from the next row, the next_date row are affected by copying the advanced date to the next row date... Is there any solution for this...
Here's my sql code that are trying to fix...
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
ORDER BY
idName,
a.fld_Date
推荐答案
试试这个：
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 (SELECT * FROM x_table ORDER BY idName, fld_Date) a
LEFT JOIN (SELECT * FROM x_table ORDER BY idName, fld_Date) b ON a.idname = b.idname AND a.fld_Date < b.fld_Date
GROUP BY a.id ORDER BY idName, fld_Date) A, (SELECT @id:=0, @idname:=0) B
这篇关于如何从fld_Date的参考字段获取下一个日期字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
