具有聚合列的每个组的SQL最新记录

I have a table similar to this:

我有一张类似的桌子:

STOCK_ID TRADE_TIME   PRICE     VOLUME  
123         1           5         100  
123         2           6         150  
456         1           7         200  
456         2           8         250

For each stock I want to get latest price (where latest is just the max trade_time) and aggregated volume, so for the above table I want to see:

对于每只股票，我想要得到最新的价格(最新的就是最大交易时间)和总交易量，因此对于上面的表，我想看到:

123  6 250  
456  8 450

I've just discovered that the current query doesn't (always) work, ie there's no guarantee that the price selected is always the latest:

我刚刚发现当前的查询并不(总是)有效(即不能保证所选的价格总是最新的):

select stock_id, price, sum(volume) group by stock_id

Is this possible to do without subqueries? Thanks!

没有子查询，这是可能的吗?谢谢!

4 个解决方案

#1

As you didn't specify the database you are using Here is some generic SQL that will do what you want.

由于您没有指定要使用的数据库，所以这里有一些通用SQL，可以执行您想要的操作。

SELECT
  b.stock_id,
  b.trade_time,
  b.price,
  a.sumVolume
FROM (SELECT
        stock_id, 
        max(trade_time) AS maxtime,
        sum(volume) as sumVolume
      FROM stocktable
      GROUP BY stock_id) a
INNER JOIN stocktable b
  ON b.stock_id = a.stock_id and b.trade_time = a.maxtime

#2

In SQL Server 2005 and up, you could use a CTE (Common Table Expression) to get what you're looking for:

在SQL Server 2005及以上版本中，您可以使用CTE (Common Table Expression)来获取您要查找的内容:

;WITH MaxStocks AS
(
    SELECT
        stock_id, price, tradetime, volume,
        ROW_NUMBER() OVER(PARTITION BY stock_ID ORDER BY TradeTime DESC) 'RowNo'
    FROM    
        @stocks
)
SELECT
    m.StockID, m.Price, 
    (SELECT SUM(VOLUME) 
     FROM maxStocks m2 
     WHERE m2.STock_ID = m.Stock_ID) AS 'TotalVolume'
FROM maxStocks m
WHERE rowno = 1

Since you want both the last trade as well as the volume of all trades for each stock, I don't see how you could do this totally without subqueries, however....

自去年贸易以及你想要的所有交易的数量为每个股票,我不明白你怎么可以这样做完全没有子查询,然而....

#3

  select a.stock_id, b.price , sum(a.volume) from tablename a
       join (select stock_id, max(trade_time), price from tablename
             group by stock_id) b 
             on a.stock_id = b.stock_id
    group by stock_id

#4

declare @Stock table(STOCK_ID int,TRADE_TIME int,PRICE int,VOLUME int)

声明@Stock表(STOCK_ID int,TRADE_TIME int,PRICE int,VOLUME int)

insert into @Stock values(123,1,5,100),(123,2,6,150),(456,1,7,200),(456,2,8,250)

插入@Stock值(123,5100),(123,6150),(456,7200),(456,8250)

Select Stock_ID,Price,(Select sum(Volume) from @Stock B where B.Stock_ID=A.Stock_ID)Volume from @Stock A where A.Trade_Time=(Select max(Trade_Time) from @Stock)

选择Stock_ID,Price，(从@Stock B中选择sum(Volume)，其中B.Stock_ID=A.Stock_ID);Trade_Time =(从@Stock选择马克斯(Trade_Time))

#1