mysql - 合并并显示查询结果

Question

我有 2 个查询:

SELECT CustomerID,count(b.BookingStatus) as 'NotComplete'
FROM Booking b, Customer c 
WHERE c.CustomerID=b.BookingCustomerID
AND(b.BookingStatus='Pending'
OR b.BookingStatus='OTW')
GROUP BY c.CustomerID


SELECT c.CustomerID, r.*
FROM Customer c,Regular r
WHERE c.CustomerID=r.RegularCID
Result:

第一个查询

第二个查询

如何将这两个结果结合在一起？

此外，还显示零(计数)。

谢谢!

<小时/>

这是我经过几个小时的尝试后得到的结果..显然这不是我想要的..

SELECT c.CustomerID,count(b.BookingStatus) as 'NotComplete',r.RegularID
FROM Booking b, Customer c 
JOIN Regular r on r.RegularCID=c.CustomerID
WHERE c.CustomerID=b.BookingCustomerID

AND (b.BookingStatus='Pending'
or b.BookingStatus='OTW'
or b.BookingStatus='Started'
or b.BookingStatus='Unclaimed'
or b.BookingStatus='Confirmed')
GROUP by r.RegularID   

Answer 1

您可以JOIN到Regular表，然后LEFT JOIN到Booking中的派生计数表> 表。我们这样做是为了避免对 Regular 表中的所有列进行 GROUP BY:

SELECT c.CustomerID, r.*, b.NotComplete
FROM Customer c
JOIN Regular r ON r.RegularCID = c.CustomerID
LEFT JOIN (SELECT BookingCustomerID, COUNT(*) AS NotComplete
           FROM Booking
           WHERE BookingStatus IN ('Pending', 'OTW', 'Started', 'Unclaimed', 'Confirmed')
           GROUP BY BookingCustomerID) b ON b.BookingCustomerID = c.CustomerID