如t1
ID date value
0 2008-1-1 1
1 2008-1-2 2
2 2008-1-1 3需要按date分组,组内取ID最大的记录的value,这样应该可行:SELECT TOP 1 a.value FROM t1 WHERE a.date=t1.date ORDER BY ID问题是,如果t1不是一个表,而是一个子查询,那应该怎么写?自查询至少要两个名称啊
ID date value
0 2008-1-1 1
1 2008-1-2 2
2 2008-1-1 3需要按date分组,组内取ID最大的记录的value,这样应该可行:SELECT TOP 1 a.value FROM t1 WHERE a.date=t1.date ORDER BY ID问题是,如果t1不是一个表,而是一个子查询,那应该怎么写?自查询至少要两个名称啊
你的SQL可以这样select date,max(value) as value
from ta
group by dateor select 1 from ta a
where not exists(select 1 from ta where a.date = date and value > a.value)
FROM t1 a INNER JOIN (SELECT MAX(ID) as ID,DATE FROM t1 GROUP BY DATE)b
ON a.ID=b.ID AND a.DATE=b.DATE
create table #t1
(
[id] int identity(1,1) primary key,
[date] datetime,
[value] int
)insert into #t1([date],[value])
select '2008-1-1',1
union all
select '2008-1-2',2
union all
select '2008-1-1',3select #t1.value
from #t1,(select max([id]) as [id] from #t1 group by [date]) as t2
where #t1.[id] = t2.[id]drop table #t1
SELECT TOP 1 a.value FROM (你滴子查询) as t1 WHERE a.date=t1.date ORDER BY ID
(
[id] int identity(1,1) primary key,
[date] datetime,
[value] int
)insert into #t1([date],[value])
select '2008-1-1',1
union all
select '2008-1-2',2
union all
select '2008-1-1',3-- 用到CTE,可以使结构看起来更清楚。
WITH tbl AS
(
SELECT date,RANK() OVER(PARTITION BY date ORDER BY ID DESC) rnk,id,value
FROM #t1
)
SELECT * FROM tbl WHERE rnk = 1