您知道一些SQL聚合工作流程吗? [英] Do you know some SQL aggregate workrounds?

问题描述

我有此表(没有主键)，我知道此查询不正确，但是它的正确等效项是什么?

I''ve got this table (without a primary key), I know this query isn''t correct, but what''s its correct equivalent ?

SELECT siloid, amount, max(tdate)
FROM siolstate
GROUP BY siloid

我想获取"siolstate"表中的所有行，其中包含每个筒仓的最新日期，我的意思是仅对"siloid"字段进行分组

我想到了类似的东西:

I want to get all the rows in "siolstate" table which has the latest dates for each silo, I mean Grouped only by of field "siloid"

I thought about something like:

SELECT *
FROM siolstate
WHERE tdate = (SELECT siloid, max(tdate) FROM siolstate GROUP BY siloid)

但由于子查询具有两个字段而无法正常工作

我也考虑过了:

but it won''t work cause the subquery has two fields

and I thought about this too:

SELECT *
FROM siolstate
WHERE max(tdate)

但您知道...您不能在WHERE子句中使用聚合.

所以...有什么想法?

but you know ... you can''t use aggregation inside WHERE clause.

so ... any ideas?

推荐答案

我认为这是您正在寻找的东西.

I think this is what your are looking for.

SELECT Temp.siloid, temp.tdate,MAIN.amount
FROM
( 
	select siloid, MAX(tdate) AS tdate
	from dbo.siolstate
	group by siloid
) Temp
INNER JOIN dbo.siolstate MAIN ON MAIN.siloid = Temp.siloid AND MAIN.tdate = Temp.tdate

这种方法怎么样

How about this approach

SELECT *
FROM siolstate
WHERE tdate = (SELECT  max(tdate) FROM siolstate as a where siolstate.siloid = a.siloid)

这篇关于您知道一些SQL聚合工作流程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！