MySQL 查询 - 仅选择特定行的最新结果？

发布于 2024-12-26 13:13:53 字数 533 浏览 3 评论 0原文

我收到此查询，如果过期优惠券的日期正好是 1 个月前，或者最后的帐单日期 (b.date) 正好是一个月，则该查询将通过 cronjob 自动创建帐单前。这些条件只能发生一次，因为具体日期永远不会是未来 1 个月前。

SELECT *

FROM `coupons` AS c 
LEFT JOIN `partners` AS p ON p.`id` = c.`pid`
LEFT JOIN `bills` AS b ON b.`pid` = c.`pid`   

WHERE 
(
CURRENT_DATE() = DATE_ADD(c.`expires`, INTERVAL 1 MONTH) // add something here?
OR
CURRENT_DATE() = DATE_ADD(b.`date`, INTERVAL 1 MONTH) 
)

我的问题是，在第一个 WHERE 条件中，我得到了多个结果。因此，我正在寻找一种仅选择最新且仅一行 b.date 的解决方案。

我将不胜感激任何帮助。

原文

I've got this query that is about to create a bill automatically via cronjob if the date of an expired coupon is exactly 1 month ago OR the last bill-date (b.date) is exactly one month ago. The conditions can only happen once, because the specific date will never be 1 month ago in the future.

SELECT *

FROM `coupons` AS c 
LEFT JOIN `partners` AS p ON p.`id` = c.`pid`
LEFT JOIN `bills` AS b ON b.`pid` = c.`pid`   

WHERE 
(
CURRENT_DATE() = DATE_ADD(c.`expires`, INTERVAL 1 MONTH) // add something here?
OR
CURRENT_DATE() = DATE_ADD(b.`date`, INTERVAL 1 MONTH) 
)

My problem is that in the first WHERE-condition I got more than 1 result. So I'm looking for a solution to only select the latest and only one row of b.date also.

I would appreciate any help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陪我终i 2025-01-02 13:13:53

是的，只需在末尾添加“LIMIT 1”：请参阅 MySQL 文档选择。

但请注意，您必须添加 ORDER BY 以确保获得“最新”行，具体取决于您的意思 - bills.date 或 coupons.expires?

回复收藏 0 原文

ㄖ落Θ余辉 2025-01-02 13:13:53

如果您想使用最新结果，请在 bills.date 行上使用 Max 函数（请参阅：http: //www.w3schools.com/sql/sql_func_max.asp）。

回复收藏 0 原文

已下线请稍等 2025-01-02 13:13:53

这里的问题是账单与合作伙伴相关，而不是与优惠券相关。例如，您的 SQL 是这样的：

  drop table if exists coupons;
  create table coupons (id int not null, pid int not null, expires date, primary key(id));
  drop table if exists bills;
  create table bills (id int not null, pid int not null, `date` date, primary key(id));
  drop table if exists partners;
  create table partners ( id int, name varchar(20), primary key(id));

我们填充一些数据：

  insert into partners values (1,'One');
  insert into coupons values (1,1,'2011-12-12'), (2,1,'2011-11-11');
  insert into bills values (1,1,'2011-12-12'), (2,1,'2011-11-11');

此时

  select * from coupons c
  left join partners p on c.pid = p.id
  left join bills b on b.pid = c.pid

返回 4 行，这是有意义的： (partner1, coupon1, bill1), (p1, c1, b2), (p1, c2, b1) ，（p1，c2，b2）。如果我们添加：

  where
  (
  current_date() = date_add(c.expires, interval 1 month)
  or
  current_date() = date_add(b.`date`, interval 1 month)
  )

我们得到 3 行：(p1, c1, b1), (p1, c1, b2), (p1, c2, b1)。为什么？因为 coupon2 或 bill2 都没有在测试中成功，而其他组合中至少有一个成功。

我怀疑您真正寻找的内容更接近于：

  select *
  from coupons c
  where
  c.id in
  (select distinct c2.id from coupons c2 
    left join partners p2 on c2.pid=p2.id
    left join bills b2 on b2.pid=c2.pid
    where
     current_date()=date_add(c2.expires, interval 1 month)
     or
     current_date()=date_add(b2.`date`, interval 1 month)
  )

这里我使用子查询来获取符合您要求的优惠券的不同优惠券 ID 列表，然后检索这些优惠券。

但是，在使用它时，我不确定您的要求是否完全有意义：您正在检索的优惠券要么恰好在一个月前到期，要么与恰好在一个月前计费的合作伙伴相关联？在这种方法中，您仅获取优惠券列表，然后需要检索最新的账单或合作伙伴详细信息（假设始终有关联的合作伙伴）？有没有？

希望有帮助，克雷格

The problem here is that the bills relate to the partners and not to the coupons. Your SQL, for example, is like this:

  drop table if exists coupons;
  create table coupons (id int not null, pid int not null, expires date, primary key(id));
  drop table if exists bills;
  create table bills (id int not null, pid int not null, `date` date, primary key(id));
  drop table if exists partners;
  create table partners ( id int, name varchar(20), primary key(id));

We populate with some data:

  insert into partners values (1,'One');
  insert into coupons values (1,1,'2011-12-12'), (2,1,'2011-11-11');
  insert into bills values (1,1,'2011-12-12'), (2,1,'2011-11-11');

At this point

  select * from coupons c
  left join partners p on c.pid = p.id
  left join bills b on b.pid = c.pid

returns 4 rows, which makes sense: (partner1, coupon1, bill1), (p1, c1, b2), (p1, c2, b1), (p1, c2, b2). If we add:

  where
  (
  current_date() = date_add(c.expires, interval 1 month)
  or
  current_date() = date_add(b.`date`, interval 1 month)
  )

We get 3 rows: (p1, c1, b1), (p1, c1, b2), (p1, c2, b1). Why? Because neither of coupon2 or bill2 succeeds on the test, whereas at least one of each of the other combinations does.

I suspect what you're really looking for is closer to:

  select *
  from coupons c
  where
  c.id in
  (select distinct c2.id from coupons c2 
    left join partners p2 on c2.pid=p2.id
    left join bills b2 on b2.pid=c2.pid
    where
     current_date()=date_add(c2.expires, interval 1 month)
     or
     current_date()=date_add(b2.`date`, interval 1 month)
  )

Here I'm using a subquery to get the list of distinct coupon ids for coupons matching your requirement, then just retrieving those coupons.

But, playing with it, I'm not sure your requirement exactly makes sense: you're retrieving coupons which either have an expiry date exactly one month ago, or which are associated with a partner who was billed exactly one month ago? In this approach, you only get the coupon list, and would then need to retrieve the latest bill or the partner details, assuming there is always an associated partner? Is there?

Hope that helps, Craig

回复收藏 0 原文