日期:2014-05-18 浏览次数:20718 次
SELECT * FROM TAB1 A WHERE NOT EXISTS ( SELECT 1 FROM TAB1 WHERE BH= A.BH AND SH = A.SH AND RQ > A.RQ ) OR NOT EXISTS ( SELECT 1 FROM TAB1 WHERE BH= A.BH AND SH = A.SH AND RQ < A.RQ )
------解决方案--------------------
select * from tablename a where date =(select min(date) from tablename b where a.id=b.id) or date= (select max(date) c where a.id=c.id)
更正一下
------解决方案--------------------
if object_id('t','U') is not null drop table t
go
create table t
(
BH varchar(10),
SH varchar(10),
RQ datetime
)
go
insert into t
select '231' as BH,'A1' as SH,'2011-01-10' as RQ union all
select '231','A1','2011-02-16' union all
select '231','A1','2011-05-20' union all
select '365','B1','2011-01-11' union all
select '365','B1','2011-03-12' union all
select '365','B1','2011-06-08' union all
select '365','B1','2011-08-09'
go
select * from t
go
select
BH,
SH,
MIN(RQ) as date_RQ
From t
group by BH,SH
union all
select
BH,
SH,
Max(RQ)
From t
group by BH,SH
--231 A1 2011-01-10 00:00:00.000
--365 B1 2011-01-11 00:00:00.000
--231 A1 2011-05-20 00:00:00.000
--365 B1 2011-08-09 00:00:00.000