SQL 分组查询问题
字段1 字段2 字段3
25785 K144 2012-07-01 00:00:00.000
25787 K144 2012-08-01 00:00:00.000
25789 K144 2012-08-01 00:00:00.000
25790 L155 2012-07-01 00:00:00.000
25791 L155 2012-09-01 00:00:00.000
25793 D139 2012-07-01 00:00:00.000
26201 D139 2011-11-01 00:00:00.000
32761 D139 2013-07-01 00:00:00.000
字段1是唯一的，字段2 如，K144是一组数据，L155 ，D139 ，我要找到每组时间最大数据
查询最新数据，结果如下
25787 K144 2012-08-01 00:00:00.000
25789 K144 2012-08-01 00:00:00.000
25791 L155 2012-09-01 00:00:00.000
26201 D139 2011-11-01 00:00:00.000
[解决办法]
select *
from
(
select *,
ROW_NUMBER() over(partition by 字段2 order by 字段3 desc) rownum
from tb
)t
where rownum = 1
select *
from
(
select *,
dense_rank() over(partition by 字段2 order by 字段3 desc) rownum
from tb
)t
where rownum = 1
create table #tab(字段1 int, 字段2 varchar(50), 字段3 datetime)
insert into #tab
select 25785, 'K144', '2012-07-01 00:00:00.000' union all
select 25787, 'K144', '2012-08-01 00:00:00.000' union all
select 25789, 'K144', '2012-08-01 00:00:00.000' union all
select 25790, 'L155', '2012-07-01 00:00:00.000' union all
select 25791, 'L155', '2012-09-01 00:00:00.000' union all
select 25793, 'D139', '2012-07-01 00:00:00.000' union all
select 26201, 'D139', '2011-11-01 00:00:00.000' union all
select 32761, 'D139', '2013-07-01 00:00:00.000'
select * from #tab
select * from (
select row_number()over(partition by 字段2 order by 字段3 desc)id,* from #tab
)a where id=1
----------------------------------------------------------
id 字段1 字段2 字段3
-------------------- ----------- -------------------------------------------------- -----------------------
1 32761 D139 2013-07-01 00:00:00.000
1 25787 K144 2012-08-01 00:00:00.000
1 25791 L155 2012-09-01 00:00:00.000
(3 行受影响)
