从两列
问题描述:
我有以下结构的表中选择不同的值:从两列
itemId | direction | uid | created
133 0 17 1268497139
432 1 140 1268497423
133 0 17 1268498130
133 1 17 1268501451
我需要为两列中选择不同的值 - itemId
和direction
,所以输出会是这样:
itemId | direction | uid | created
432 1 140 1268497423
133 0 17 1268498130
133 1 17 1268501451
在原始表格中,我们有两行itemId
- 133和direction
- 0,但我们只需要其中一行具有最新创建时间。
谢谢你的任何建议!
答
用途:
SELECT t.itemid,
t.direction,
t.uid,
t.created
FROM TABLE t
JOIN (SELECT a.itemid,
MAX(a.created) AS max_created
FROM TABLE a
GROUP BY a.itemid) b ON b.itemid = t.itemid
AND b.max_created = t.created
你必须使用集合(IE:MAX),以获得每itemid的最大创造价值,并加入该到表的一个不变的副本,以获得与关联的值每个itemid的最大创建值。
答
select t1.itemid, t1.direction, t1.uid, t1.created
from (select t2.itemid, t2.direction, t2.created as maxdate
from tbl t2
group by itemid, direction) x
inner join tbl t1
on t1.itemid = x.itemid
and t1.direction = x.direction
and t1.created = x.maxdate