sql按功能分组 [英] sql group by function

问题描述

我只需要获取每个 productId 具有最高交易时间的行.所以在这种情况下，我需要获取第一行，而 productid 为 224 的所有其他行都应该消失.我怎样才能解决这个问题?现在我按 NQ 分组，但有多行，因为 NQ 因每个事务而变化.我也不能取 SUM，因为那样它会将所有内容加起来，而不是在特定交易时间取 NQ.非常感谢帮助

I need to only get the line with the highest transactiontime per productId. So In this case I need to get the first line and all the other lines with productid 224 should be gone. How can I fix this? Now I group by NQ but there are multiple lines because the NQ changes by every transaction. I also can not take a SUM because then it would add up everything instead of taking the NQ at that certain transaction time. Help is much appreciated

SELECT NQ, ProductId, Product, Warehouse, ProductType, MAX(Transactiontime) as 'TransactionTime'
FROM @MaxTime
GROUP BY NQ, Productid, Product, Warehouse, ProductType 
ORDER BY ProductId

推荐答案

您可以:

SELECT  MT.NQ, MT.ProductId, MT.Product, MT.Warehouse, MT.ProductType, MT.Transactiontime
FROM    @MaxTime MT
INNER JOIN (
                SELECT  ProductId
                ,       MAX(Transactiontime) AS 'TransactionTime'
                FROM    @MaxTime
                GROUP BY Productid
            ) GR
        ON  MT.ProductId = GR.ProductId
        AND MT.TransactionTime = GR.TransactionTime
ORDER BY MT.ProductId

这篇关于sql按功能分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！