MySQL 从位于具有通用 ID 的不同表中的日期获取最接近给定日期的 future 日期

Question

我有两张 table - 客户和宴会

Client Table
----------------------------
ID     NAME
1      John
2      Jigar
3      Jiten

----------------------------
Banquet Table
----------------------------
ID     CLIENT_ID   DATED    
1      1           2016.2.3
2      2           2016.2.5
3      2           2016.2.8
4      3           2016.2.6
5      1           2016.2.9
6      2           2016.2.5
7      2           2016.2.8
8      3           2016.2.6
9      1           2016.2.7

----------------------------
:::::::::: **Required Result**
----------------------------
ID     NAME          DATED
2      Jigar         2016.2.5
3      Jiten         2016.2.6
1      John          2016.2.7

The result to be generated is such that

1. The Date which is FUTURE : CLOSEST or EQUAL to the current date, which is further related to the respective client should be filtered and ordered in format given in Required Result

当前案例的 CURDATE() 是 5.2.2016

失败:查询逻辑 1

SELECT c.id, c.name, b.dated
FROM client AS c, banquet AS b
WHERE c.id = b.client_id AND b.dated >= CURDATE()
ORDER BY (b.dated - CURDATE());

------------------------------------------- OUTPUT
ID     NAME          DATED
2      Jigar         2016.2.5
2      Jigar         2016.2.5
3      Jiten         2016.2.6
3      Jiten         2016.2.6
1      John          2016.2.7
2      Jigar         2016.2.8
2      Jigar         2016.2.8
1      John          2016.2.9

失败:查询逻辑 2

SELECT c.id, c.name, b.dated
FROM client AS c, banquet AS b
   WHERE b.dated = (
       SELECT MIN(b.dated)
       FROM banquet as b
       WHERE b.client_id = c.id
           AND b.dated >= CURDATE()
   )
ORDER BY (b.dated - CURDATE());

------------------------------------------- OUTPUT
ID     NAME          DATED
2      Jigar         2016.2.5
2      Jigar         2016.2.5
3      Jiten         2016.2.6
3      Jiten         2016.2.6
1      John          2016.2.7

sqlfiddle

UPDATE : Further result to be generated is such that

2. Clients WITHOUT : DATED should also be listed : may be with a NULL

3. the information other then DATED in the BANQUET table also need to be listed

更新的必需结果

ID     NAME          DATED        MEAL
2      Jigar         2016.2.5     lunch
3      Jiten         2016.2.6     breakfast
1      John          2016.2.7     dinner
4      Junior        -            -
5      Master        -            supper

Answer 1

对于此查询，我建议应用您的 WHERE 条件 >= CURDATE() 然后 SELECT MIN(dated) 与 GROUP BY client_id:

SELECT b.client_id, MIN(b.dated) FROM banquet b
WHERE b.dated >= CURDATE()
GROUP BY b.client_id;

由此，您可以将必要的JOIN 添加到客户表中以获取客户名称:

SELECT b.client_id, c.name, MIN(b.dated) FROM banquet b
INNER JOIN client c
ON c.id = b.client_id
WHERE b.dated >= CURDATE()
GROUP BY b.client_id;

SQLFiddle:http://sqlfiddle.com/#!9/aded8/18

编辑以反射(reflect)问题的新部分:

根据您添加的新信息 - 询问如何处理空值和“膳食”列，我进行了一些更改。此更新的查询处理日期中可能的空值(通过调整 WHERE 子句)，并且还包括膳食信息。

SELECT b.client_id, c.name, 
MIN(b.dated) AS dated,
IFNULL(b.meal, '-') AS meal
FROM banquet b
INNER JOIN client c
ON c.id = b.client_id
WHERE b.dated >= CURDATE() OR b.dated IS NULL
GROUP BY b.client_id;

或者您可以采用其中的一些内容并将其与 Gordon Linoff 的回答结合起来，这听起来总体上会表现得更好。

新 SQLFiddle:http://sqlfiddle.com/#!9/a4055/2