多连接查询以返回最近发货的产品的记录

laik7k3q 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(174)

我的目的是建立一个我公司产品的目录。我们的大多数产品都有多个id和产品（product.num），但是一个产品的所有可能的id和产品的描述都是相同的。我可以返回我们的产品名称和正确的最后发货日期列表，但返回的产品编号不是实际的最新#（它们确实属于该产品，只是不是最近发货的）。以下是目前的问题：

SELECT part.num, part.description, product.upc, vendorparts.lastCost, producttree.name, Max(ship.dateShipped)

FROM ship
LEFT JOIN shipitem ON ship.id = shipitem.shipid
LEFT JOIN soitem ON shipitem.soitemid = soitem.id
LEFT JOIN product ON soitem.productid = product.id
LEFT JOIN producttotree ON product.id = producttotree.productid 
LEFT JOIN producttree ON producttotree.producttreeid = producttree.id
LEFT JOIN part ON product.partid = part.id
LEFT JOIN vendorparts ON part.id = vendorparts.partid

GROUP BY product.description

sql mysql Join left-join

来源：https://stackoverflow.com/questions/63060076/multi-join-query-to-return-record-for-the-most-recently-shipped-product

1条答案

按热度按时间

kx5bkwkv1#

从版本8开始，mysql支持窗口函数

SELECT DISTINCT 
    FIRST_VALUE(part.num) OVER (PARTITION BY product.description ORDER BY ship.dateShipped DESC) as num,
    FIRST_VALUE(part.description) OVER (PARTITION BY product.description ORDER BY ship.dateShipped DESC) as description,
    FIRST_VALUE(product.upc) OVER (PARTITION BY product.description ORDER BY ship.dateShipped DESC) as upc,
    FIRST_VALUE(vendorparts.lastCost) OVER (PARTITION BY product.description ORDER BY ship.dateShipped DESC) as lastCost,
    FIRST_VALUE(producttree.name) OVER (PARTITION BY product.description ORDER BY ship.dateShipped DESC) as name,
    FIRST_VALUE(ship.dateShipped) OVER (PARTITION BY product.description ORDER BY ship.dateShipped DESC) as dateShipped
FROM ship
LEFT JOIN shipitem ON ship.id = shipitem.shipid
LEFT JOIN soitem ON shipitem.soitemid = soitem.id
LEFT JOIN product ON soitem.productid = product.id
LEFT JOIN producttotree ON product.id = producttotree.productid 
LEFT JOIN producttree ON producttotree.producttreeid = producttree.id
LEFT JOIN part ON product.partid = part.id
LEFT JOIN vendorparts ON part.id = vendorparts.partid

赞(0）回复(0）举报 2021-07-24

我来回答

多连接查询以返回最近发货的产品的记录

1条答案

相关问题

热门标签

最新问答