sql分组取最大最小

最新推荐文章于 2023-12-21 15:03:10 发布

原创最新推荐文章于 2023-12-21 15:03:10 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #table #insert #object #测试

--测试数据
if object_id('tempdb..#t') is not null
drop table #t
create table #t(
col1 varchar(50),
col2 varchar(50),
col3 numeric(18,6))

insert into #t 
select 'HRB16','2012-06-14 04:52:55.000','93.99' union all 
 select 'HRC33','2012-06-15 23:11:14.000','88.50' union all 
 select 'HRC33','2012-06-16 07:07:23.000','82.82' union all 
 select 'HRC35','2012-06-15 07:57:44.000','95.86' union all 
 select 'HRC35','2012-06-16 09:38:30.000','93.99' union all 
 select 'HRC36','2012-06-16 12:58:14.000','80.32' union all
select 'HRC36','2012-06-16 12:59:14.000','81.32' 

--sql语句


select min(col3) from #t a Where exists(select 1 From #t b where col1 = a.col1 and col2 < a.col2)

select min(col3) from #t a where col2 = (select max(col2) from #t b where a.col1 = b.col1)