SQL查询-与多个表分组 [英] SQL Query - Grouping with multiple tables
问题描述
此问题涉及2个表:
表1:
|- Time Stamp -|- Special Number -|- Other Data -|
|- 2011 -|- 1 -|- green -|
|- 2010 -|- 1 -|- blue -|
|- 2009 -|- 2 -|- yellow -|
|- 2011 -|- 3 -|- red -|
|- 2010 -|- 3 -|- orange -|
|- 2009 -|- 4 -|- purple -|
表2:
|- Special Number -|- State (location) -|
|- 1 -|- Hawaii -|
|- 2 -|- Hawaii -|
|- 3 -|- Alaska -|
|- 4 -|- Alaska -|
表2将特殊编号"与州"相关联
Table 2 relates the 'Special Number' to a 'State'
现在,我尝试获得的结果将类似于:
Now then, the result I am trying to get would look something like:
|- Time Stamp -|- State (location) -|- Other Data -|
|- 2011 -|- Hawaii -|- green -|
|- 2011 -|- Alaska -|- red -|
我要在哪里获取MAX时间戳(按每个州分组)以及表1中与该最新时间戳"行相对应的其他数据.
Where I am trying to get the MAX time stamp, grouped by each state, as well as the other data corresponding to that 'latest time stamp' row from Table 1.
如果我这样做:
SELECT MAX(time stamp), state
FROM table 1, table 2
WHERE table 1.special number = table 2.special number
GROUP BY state
这将返回每个状态的最大时间戳(这几乎是我想要的状态),但是当我尝试包含其他数据"时,它将返回所有记录(因为每个其他数据"记录都是唯一的) ).
This returns the max time stamp, for each state (which is almost what I am looking for), but when I try to include 'Other Data', it returns all the records (since every 'other data' record is unique).
我希望有人能提供一些想法, 谢谢
I hope someone can provide some ideas, Thanks
表1的唯一ID"列:
|- Time Stamp -|- Special Number -|- Other Data -|- Unique Row ID -|
|- 2011 -|- 1 -|- green -| 0 -|
|- 2010 -|- 1 -|- blue -| 1 -|
|- 2009 -|- 2 -|- yellow -| 2 -|
|- 2011 -|- 3 -|- red -| 3 -|
|- 2010 -|- 3 -|- orange -| 4 -|
|- 2009 -|- 4 -|- purple -| 5 -|
解决方案* * * *感谢所有发布者! * * * *
EDIT 2: SOLUTION * * * * Thanks to everyone who posted ! * * * *
SELECT t1.timestamp, t2.specialNumber, t1.otherData
FROM Table1 t1 inner join Table2 t2 on t1.specialNumber = t2.specialNumber
inner join (select MAX(Table1.timestamp) maxts, Table2.state
from Table1 inner join Table2 on Table1.specialNumber = Table2.specialNumber
group by Table2.state) t3
on t2.state = t3.state and t1.timestamp = t3.maxts
* * *
推荐答案
啊,好老的 greatest-n-per-group ...
这是一种方法:
SELECT t1.TimeStamp, t2.State, t1.OtherData
FROM Table1 t1
inner join Table2 t2
on t1.SpecialNumber = t2.SpecialNumber
inner join (SELECT MAX(time stamp) maxts, state
FROM table1 inner join table2
ON table1.specialnumber = table2.specialnumber
GROUP BY state) t3
on t2.State = t3.State and t1.TimeStamp = t3.maxts
There's a very comprehensive answer here: SQL Select only rows with Max Value on a Column
这篇关于SQL查询-与多个表分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!