mysql - 根据另一个表结果选择并分组

我有两个桌子。酒店服务和客人订单

酒店服务

s_id(pk)    serviceType    serviceName

guestOrder表：

g_id(pk)    serviceType    totalAmount    balanceDue    PaidAmount    orderDate

我想从hotelservice表中获取基于serviceType的totalAmount，balanceDue，PaidAmount的总和。

也就是说，它将基于serviceType列对hotelservices.serviceType进行迭代和分组，并从guestorder表中获取关联的数据。

到目前为止，我的尝试：

SELECT
 g.OrderDate as saleDate,
 g.ServiceType,
 sum(g.TotalAmount) as TotalSales,
 sum(g.BalanceDue) as TotalBalanceDue,
 sum(g.PaidAmount) as TotalPaid,
 h.ServicesType as services
 FROM guestorder as g join hotelservices as h on g.ServiceType=h.ServicesType
 group by saleDate

但这不是我想要的。我需要它显示其他serviceType，即使guestorder中没有任何内容。我也不知道我是否有意义。提前致谢

最佳答案

也许可以颠倒您的联接，将其变成左外联接，然后使用GROUP BY来解决问题，例如：

SELECT
    g.OrderDate as saleDate,
    g.ServiceType,
    sum(g.TotalAmount) as TotalSales,
    sum(g.BalanceDue) as TotalBalanceDue,
    sum(g.PaidAmount) as TotalPaid,
    h.ServicesType as services
FROM
    hotelservices as h
    LEFT JOIN guestorder as g ON g.ServiceType=h.ServicesType
GROUP BY
    g.OrderDate,
    g.ServiceType,
    h.ServicesType;

关于mysql - 根据另一个表结果选择并分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25431028/