php - 如何从具有多个 ORDER BY mysql 的多个表中获取记录
问题描述
我有三个表,我想根据所有表的日期时间获取记录,这是我的表“用户”
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 14: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) 我该怎么做?我想要查询后的结果
id bookingId userId createdOn
1 100 1 2020-07-11 14:31:28 //datetime is in desending order( data coming from cancelPaymentHistory)
2 105 1 2020-07-11 12:31:19 // data coming from paymenthistory
3 102 1 2020-07-11 10:31:19 // data coming from paymentHistory
4 101 1 2020-07-10 12:11:14 // data coming from paymenthistory
...
我尝试使用以下代码,但给了我空的结果
DROP VIEW IF EXISTS sorted_data;
create view sorted_data as (SELECT bookingId, userId as user_id, userId,createdOn,createdOn as created_on, 'paymentHistory' as table_name FROM paymentHistory)
UNION ALL (SELECT bookingId, userId as user_id, userId,createdOn,createdOn as created_on, 'cancelPaymentHistory' as table_name FROM cancelPaymentHistory )
UNION ALL (SELECT bookingId, user_id, user_id as userId ,created_on as createdOn,created_on, 'usr_booking' as table_name FROM usr_booking);
解决方案
首先,您需要更正视图创建代码:
DROP VIEW IF EXISTS sorted_data;
CREATE VIEW sorted_data
AS
(SELECT id, booking_id, user_id,created_on, 'paymentHistory' as table_name
FROM paymentHistory)
UNION ALL
(SELECT id, booking_id, user_id, created_on, 'cancelPaymentHistory' as table_name
FROM cancelPaymentHistory )
UNION ALL
(SELECT id, booking_id, user_id, created_on, 'usr_booking' as table_name
FROM usr_booking);
执行此查询后,您有视图,并且此代码返回空结果是正确的 - 它只是创建视图。但是你需要从中做出选择。执行这个:
SELECT *
FROM sorted_data
ORDER BY created_on
结果在这里:https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b7e77108d633d73c7732f7d36430a6d9
但我认为你的结构不是最优的。如果我是你,我只会制作三张桌子users
:orders
和order_states
。您将只有一张表,其中包含所有用户的订单、状态和您需要的所有信息,您可以通过简单的 SELECT 获得。