sql - SQL - 在一对多关系中加入最新的 1 值

Question

我有以下查询，其中显示有关“厨师”的信息，以及他们为食品服务应用程序出售的食品。它显示了每个卖家、一些个人信息以及他们在平台上销售的食品。

这是一个 postgresql 数据库。

当前查询引用并汇总来自 7 个不同表的数据。我在下面包含了每个表的图表，以及它们之间的链接关系。我同意，此查询应拆分为单独的更相关的报告，但这是客户要求的。

这个查询几乎完成了，最后的要求是以某种方式包含一个列来显示每个食品项目的最近订购时间（orders.time_placed）。

想到一个主要问题：

这甚至可能吗？考虑到“订单”表没有包含所购买食品的列。“订单”表仅链接回“商店”表......这让我相信我能做的最好的事情就是拉动每家商店的最后一次销售时间（而不是降低到项目级别）。

在四处搜索时，我发现了一些与我非常相似的情况——尤其是这个：link

尽管我在实施该解决方案方面没有成功。我让这太复杂了吗？

这是我当前的查询：

select 
account.id as "Account ID",
account.firstname as "Seller First Name", 
account.lastname as "Seller Last Name",
account.email as "Seller Email",
account.phone as "Seller Phone",
address.address as "Seller Address (Street)",
address.address_2 as "Seller Address 2",
account.zip_code as "Seller Zip",
address.neighborhood as "Seller Neighborhood",
menu.name as "Name of active menu",
kitchen_item.name as "Dishes", 
kitchen_item.price as "Price",
kitchen_item.daily_max_orders as "Quantity",
menu.pickup_start_time as "Start time", 
menu.pickup_end_time as "End time",
menu.repeat_mon as "Monday",
menu.repeat_tues as "Tuesday",
menu.repeat_wed as "Wednesday",
menu.repeat_thurs as "Thursday",
menu.repeat_fri as "Friday",
menu.repeat_sat as "Saturday", 
menu.repeat_sun as "Sunday"
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id
group by account.id, account.firstname, account.lastname, account.email, account.phone, address.address, address.address_2, account.zip_code, address.neighborhood, menu.name, kitchen_item.name, kitchen_item.price, kitchen_item.daily_max_orders, menu.pickup_start_time, menu.pickup_end_time, menu.repeat_mon, menu.repeat_tues, menu.repeat_wed, menu.repeat_thurs, menu.repeat_fri, menu.repeat_sat, menu.repeat_sun
order by account.id asc;

这是我试图从上面的链接中借用的解决方案......令人惊讶的是，它不起作用:)（插入到最后一个 JOIN 的正下方）：

inner join (
    SELECT orders.store_id,
    MAX(orders.placed) maxdate
    from orders
    group by orders.store_id
    ) maxdates on account.id = maxdates.buyer_account_id INNER JOIN
    orders o on maxdates.store_id = store.id
    and maxdates.maxdate = o.placed

有没有更简单的方法来完成这个看似简单的任务？谢谢

编辑 - 我很抱歉，我意识到这对我来说有点太多了，除了屏幕截图之外提供示例数据和预期输出。链接问题中有一个简化的示例，但我不知道如何将逻辑应用于我的查询。

标签： sqlpostgresqljoin

Answer 1

正如您所怀疑的，您无法查询您没有的数据。由于您的架构不包含有关何时订购某些商品的任何信息，因此您无法检索该信息。

要获得商店中的最新销售，您可以使用 postgresql 的 DISTINCT ON（请参阅docs）。

select distinct on (account.id, kitchen_item.name)
account.id as "Account ID",
account.firstname as "Seller First Name", 
account.lastname as "Seller Last Name",
account.email as "Seller Email",
account.phone as "Seller Phone",
address.address as "Seller Address (Street)",
address.address_2 as "Seller Address 2",
account.zip_code as "Seller Zip",
address.neighborhood as "Seller Neighborhood",
menu.name as "Name of active menu",
kitchen_item.name as "Dishes", 
kitchen_item.price as "Price",
kitchen_item.daily_max_orders as "Quantity",
menu.pickup_start_time as "Start time", 
menu.pickup_end_time as "End time",
menu.repeat_mon as "Monday",
menu.repeat_tues as "Tuesday",
menu.repeat_wed as "Wednesday",
menu.repeat_thurs as "Thursday",
menu.repeat_fri as "Friday",
menu.repeat_sat as "Saturday", 
menu.repeat_sun as "Sunday",
orders.time_placed as "Last Store Sale"
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
left join orders on (orders.store_id = store.id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id
order by account.id asc, kitchen_item.name asc, orders.time_placed desc;