无重复数据的mysql最近日期

xytpbqjk 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(265)

因此，我需要显示我的所有客户和与相关的预订号码（空，如果没有预订）没有重复的客户。如果客户有很多预订，我只需要显示最近的预订日期。我不明白为什么我的查询不起作用。
以下是is所做的：http://sqlfiddle.com/#!9/df0455/19号

SELECT c.name, x.number, x.start_date
FROM customer c 
LEFT JOIN 
(SELECT b.customer_id, b.number, b.start_date
 FROM booking b
 INNER JOIN (
    SELECT customer_id, MIN(ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date)))) as mindiff
    FROM booking
    GROUP BY customer_id
  ) nearest ON b.customer_id = nearest.customer_id AND ABS(TIME_TO_SEC(TIMEDIFF(NOW(), start_date))) = mindiff
) AS x ON c.id = x.customer_id

事实上 Paul 显示三次，需要的是显示 Paul 就一次，最近的订票号码是谁 booking-1 2019-11-05 21:45:00 我希望你能帮助我

sql mysql Join Date greatest-n-per-group

来源：https://stackoverflow.com/questions/62174225/mysql-nearest-date-without-duplicated-data

2条答案

按热度按时间

fv2wmkja1#

可以使用行限制相关子查询进行筛选：

select c.name, b.number, b.start_date
from customer c
inner join booking b on b.customer_id = c.id
where b.start_date = (
    select b1.start_date
    from booking b1
    where b1.customer_id = b.customer_id
    order by abs(timestampdiff(second, now(), b1.start_date)) 
    limit 1
)

在db小提琴中，这会产生：

name    number     start_date
Paul    booking-1   2019-11-05T21:45:00Z
John    booking-3   2019-09-27T21:45:00Z
Morgan  booking-5   2019-09-27T21:45:00Z

如果您还想显示没有预订的客户，那么您可以 left join 并将过滤移到 on 合同条款 join :

select c.name, b.number, b.start_date
from customer c
left join booking b 
    on b.customer_id = c.id
    and b.start_date = (
        select b1.start_date
        from booking b1
        where b1.customer_id = b.customer_id
        order by abs(timestampdiff(second, now(), b1.start_date)) 
        limit 1
    )

赞(0）回复(0）举报 2021-07-26

plupiseo2#

你可以用 NOT EXISTS 要获得最近的预订并加入客户：

SELECT c.id, c.name, t.number, t.start_date
FROM customer c 
LEFT JOIN (
  SELECT b.* FROM booking b
  WHERE NOT EXISTS (
    SELECT 1 FROM booking
    WHERE customer_id = b.customer_id 
      AND ABS(TIMESTAMPDIFF(SECOND, NOW(), start_date)) < ABS(TIMESTAMPDIFF(SECOND, NOW(), b.start_date))
  )  
) t ON t.customer_id = c.id

请看演示。
结果：

| id  | name   | number    | start_date          |
| --- | ------ | --------- | ------------------- |
| 1   | Paul   | booking-1 | 2019-11-05 21:45:00 |
| 2   | John   | booking-3 | 2019-09-27 21:45:00 |
| 3   | Morgan | booking-5 | 2019-09-27 21:45:00 |
| 4   | Jane   |           |                     |
| 5   | Mike   |           |                     |

赞(0）回复(0）举报 2021-07-26

我来回答

无重复数据的mysql最近日期

2条答案

相关问题

热门标签

最新问答