MySQL从具有Common ID的不同表中的日期获取最近的给定日期的日期

I have two tables - Client and Banquet

我有两张桌子 - 客户和宴会

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

1.未来的日期:当前日期的CLOSEST或EQUAL,与相应客户进一步相关的日期应按要求结果中给出的格式进行过滤和排序

CURDATE() for current case is 5.2.2016

当前案例的CURDATE()是5.2.2016

FAILED: Query Logic 1

失败:查询逻辑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

FAILED: Query Logic 2

失败:查询逻辑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

2.还应列出没有:DATED的客户端:可能带有NULL

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

3.还需要列出BANQUET表中另外的DATED信息

UPDATED Required Result

更新的必需结果

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

4 个解决方案

#1

For this query, I suggest applying your WHERE condition >= CURDATE() and then SELECT the MIN(dated) with GROUP BY client_id:

对于此查询,我建议应用WHERE条件> = CURDATE(),然后使用GROUP BY client_id选择MIN(日期):

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

From this, you can add the necessary JOIN to the client table to get the client name:

从这里,您可以将必要的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

EDITED TO REFLECT NEW PARTS OF QUESTION:

编辑反映新的问题部分:

Based on the new info you added - asking how to handle nulls and the 'meal' column, I've made some changes. This updated query handles possible null values (by adjusting the WHERE clause) in dated, and also includes meal information.

根据您添加的新信息 - 询问如何处理空值和“餐”列,我做了一些更改。此更新的查询处理过时的可能空值(通过调整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;

or you can take some of this and combine it with Gordon Linoff's answer, which sounds like it will perform better overall.

或者你可以采取一些这个并将它与戈登林诺夫的答案结合起来,这听起来总体上会表现得更好。

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

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

#2

One approach uses a correlated subquery:

一种方法使用相关子查询:

select c.*,
       (select max(dated)
        from banquet b
        where b.client_id = c.id and
              b.dated >= CURDATE()
       ) as dated
from client c;

Then, I would recommend an index on banquet(client_id, dated).

然后,我会推荐一个关于宴会的索引(client_id,日期)。

The advantage of this approach is performance. It does not require an aggregation over the entire client table. In fact, the correlated subquery can take advantage of the index, so the query should have good performance.

这种方法的优点是性能。它不需要在整个客户端表上进行聚合。实际上,相关子查询可以利用索引,因此查询应该具有良好的性能。

#3

Try This

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

if you want to limited output like your expected result is 3 record so you can use limit in this case

如果你想限制输出,就像你预期的结果是3记录,所以你可以在这种情况下使用限制

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

if you want to use join with Client table you can use `join'

如果你想使用与Client表的连接你可以使用`join'

SELECT banquet.client_id, MIN(banquet.dated) 
FROM banquet JOIN Client 
ON Client.id = banquet.client_id
WHERE banquet.dated >= CURDATE()
GROUP BY banquet.client_id;

also use limit with last query.

也使用上次查询的限制。

#4

The Date which is CLOSEST or EQUAL to the current date

与当前日期相关的日期为CLOSEST或EQUAL

To closely rspond to your requirements, i. e. "CLOSEST" may be both before or after the current date, the query must use the (absolute) difference between it and the DATED value.

为了满足您的要求,我。即“CLOSEST”可能在当前日期之前或之后,查询必须使用它与DATED值之间的(绝对)差异。

So it can be achieved like this:

所以它可以像这样实现:

SELECT 
  c.id, c.name, b.dated,
  MIN(ABS(TIMESTAMPDIFF(DAY, b.dated, CURDATE()))) AS `delta`
FROM banquet AS b
  INNER JOIN client AS c ON b.client_id = c.id
GROUP BY b.client_id

Here is the SQL fiddle

这是SQL小提琴

#1