mysql - 使用 UNION 优化 mysql 查询

Question

我需要获取下一个日期和上一个日期

表结构如下

| auto_id | id | next_date  | next_activity |
|    1    |  1 | 22-12-2012 |     -         |
|    2    |  1 | 25-12-2012 |     -         |
|    3    |  1 | 26-12-2012 |     -         |
|    4    |  1 | 28-12-2012 |     -         |

所以我需要 next_day 和 previous_daynext_day = 当日之后的下一个日期previous_day = current_date 之前的 next_date

(SELECT * FROM `activity` WHERE id = 1 and next_date > CURDATE() order by next_date asc limit 1)
UNION
(SELECT * FROM `activity` WHERE id = 1 and next_date = CURDATE() )
UNION
(SELECT * FROM `activity` WHERE id = 1 and next_date < CURDATE() order by next_date desc limit 1)
ORDER BY next_date desc limit 2

其他方法可以自连接表...

有没有办法优化表格

Answer 1

这是另一种方法:

SELECT next_date, date_diff
 FROM (SELECT *,
               @dateDiff := datediff(next_date, curdate()) AS date_diff,
               @pDateDiff :=
                  IF((@dateDiff < 0 AND @dateDiff > @pDateDiff),
                     @dateDiff,
                     @pDateDiff)
                  AS pDateDiff,
               @nDateDiff :=
                  IF((@dateDiff > 0 AND @dateDiff < @nDateDiff),
                     @dateDiff,
                     @nDateDiff)
                  AS nDateDiff
          FROM activity, (SELECT @pDateDiff := -9999, @nDateDiff := 9999) tmp
          WHERE id = 1) aView
 WHERE date_diff IN (@pDateDiff, 0, @nDateDiff)
ORDER BY next_date;

date_diff 值提供了上一个日期和下一个日期的视角。

• 选择 id = 1 的所有日期
• 查找 next_date 和 curdate() 之间的日期差异，并将其存储在用户定义的变量 @dateDiff 中。
• @pDateDiff 是另一个变量，用于跟踪负 @dateDiff 值(我们之前的日期)中的最大值
• @nDateDiff 是另一个变量，它跟踪正 @dateDiff 值中的最小值(我们的下一个日期)
• 最后，仅选择 (-ve max, 0, +ve min) 中的日期。

PS:如果您有重复的日期条目，则查询可能会返回所有这些条目。