php - 如何获取具有不同日期的重复行

Question

我的 table

| ID |   Date   | Next Date | 
| 1  |2014-12-23| 2014-12-25|
| 2  |2014-12-20| 2014-12-22|
| 3  |2014-12-19| 2014-12-21|
| 4  |2014-12-15| 2014-12-18|

在搜索结果查询的日期范围内搜索时如何显示重复项:

select * from my_table where date >= '2014-12-14' AND date <='2015-01-22' ORDER BY (date) ASC 

我的结果是:

1. ID: 4, Date: 2014-12-15, Next Date: 2014-12-18
2. ID: 3, Date: 2014-12-19, Next Date: 2014-12-21
3. ID: 2, Date: 2014-12-20, Next Date: 2014-12-22
4. ID: 1, Date: 2014-12-23, Next Date: 2014-12-25 

我想要做的是显示带有日期、下一个日期的重复项，并按日期、下一个日期排序。

1. ID: 4, Date: *2014-12-15*
2. ID: 4, Next Date: *2014-12-18*
3. ID: 3, Date: *2014-12-19*
4. ID: 2, Date: *2014-12-20*
5. ID: 3, Next Date: *2014-12-21*
6. ID: 2, Next Date: *2014-12-22*
7. ID: 1, Date: *2014-12-23*
8. ID: 1, Next Date: *2014-12-25*  

对于我的情况来说，什么是一个好的解决方案？

谢谢

Answer 1

我想这就是你想要的:

select "ID", "Order Date"
from
(
    (select "ID", "Date", "Next Date", "Date" as "Order Date" from Table1)
    union all
    (select "ID", "Date", "Next Date", "Next Date" as "Order Date" from Table1)
) as Table2
order by "Order Date"

注意:将 Table1 更改为表的实际名称。