时间戳

首页 > 解决方案 > 如何从多个表Mysql Php中选择最新记录

php - 如何从多个表Mysql Php中选择最新记录

问题描述

我有三个表，我想根据所有表的日期时间获取记录，

这是我的表“用户”

id      name
1       abc
2       xyz

这是我的表“paymentHistory”

id      bookingId           userId          createdOn
1       101                 1               2020-07-10 12:11:14
2       102                 1               2020-07-11 10:31:19 
3       105                 1               2020-07-11 12:31:19 
4       109                 2               2020-07-10 11:45:32

这是我的表“cancelPaymentHistory”

id      bookingId           userId          createdOn
1       103                 1               2020-07-07 11:31:28
2       100                 1               2020-07-11 11:31:28
3       109                 2               2020-07-08 19:28:41

这是我的表“usr_booking”

id      bookingId           userId          status          created_on
1       104                 1               Inprocess       2020-07-07 10:31:28

现在我想获取 userId='1' 的所有记录，并根据三个表的 createdOn (DESC) 我该怎么做？

我尝试使用以下代码但显示了这么多记录（显示错误的结果）

SELECT u.id as userId,ph.bookingId as paymentHistoryBooking,cph.bookingId as CancelBookingId,ub.bookingId as usrBooking
FROM users u
JOIN paymentHistory ph ON u.id=ph.userId
JOIN cancelPaymentHistory cph ON u.id=cph.id
JOIN usr_booking ub ON u.id=ub.userId
WHERE u.id='152'

标签： phpmysqljoin

解决方案

您的查询不正确。首先，您错过了表的别名u，users因此第二行必须如下所示：

FROM users u

第二，在第四行你不小心写了u.id = cph.id，但是你想匹配的是userId，而不是cancelPaymentHistory.id。此外，您错过了cancelPaymentHistory表的别名。所以正确的第四行必须如下所示：

JOIN cancelPaymentHistory cph ON u.id = cph.userId

整个查询看起来像：

SELECT u.id as userId,ph.bookingId as paymentHistoryBooking,cph.bookingId as CancelBookingId,ub.bookingId as usrBooking 
FROM users u
JOIN paymentHistory ph ON u.id=ph.userId
JOIN cancelPaymentHistory cph ON u.id=cph.userId
JOIN usr_booking ub ON u.id=ub.userId
WHERE u.id='152';

要按createdOn所有表中最新的排序结果，您必须首先确定createdOn所有表中的最新：

SELECT userId, max(createdOn) as latestActivity
(SELECT userId, max(createdOn) as createdOn
FROM cancelPaymentHistory
UNION
SELECT userId max(createdOn) as createdOn
FROM usr_booking
UNION 
SELECT userId max(createdOn) as createdOn
FROM paymentHistory)

此结果必须与先前的查询连接：

SELECT u.id as userId,ph.bookingId as paymentHistoryBooking,cph.bookingId as CancelBookingId,ub.bookingId as usrBooking 
, max(latestActivity)
FROM users u
JOIN paymentHistory ph ON u.id=ph.userId
JOIN cancelPaymentHistory cph ON u.id=cph.userId
JOIN usr_booking ub ON u.id=ub.userId
JOIN (SELECT userId, max(createdOn) as latestActivity
     FROM (SELECT userId, max(createdOn) as createdOn
     FROM cancelPaymentHistory
     UNION
     SELECT userId, max(created_on) as created_on
     FROM usr_booking
     UNION 
     SELECT userId, max(createdOn) as createdOn
     FROM paymentHistory) a) activity ON activity.userId = u.id
WHERE u.id='1'
ORDER BY activity.latestActivity DESC;

请注意，我仍然ORDER BY在查询中得到了。我假设你想为多个用户运行这个语句，所以你可以修改WHERE-Statement WHERE u.id IN ('1','2','3')并且仍然有一个排序列表。

我已经为你设置了一个小提琴http://sqlfiddle.com/#!9/ea835a/28/0

推荐阅读