日期:2014-05-18  浏览次数:20553 次

如何同时取出最小日期和最大日期的记录?
TAB1

BH SH RQ  
231 A1 2011-01-10
231 A1 2011-02-16
231 A1 2011-05-20
365 B1 2011-01-11
365 B1 2011-03-12
365 B1 2011-06-08
365 B1 2011-08-09  
。。。

说明:在BH、SH相同的情况下,只取日期最小和最大得记录

想要的结果 

BH SH RQ  
231 A1 2011-01-10
231 A1 2011-05-20
365 B1 2011-01-11
365 B1 2011-08-09  

请高手帮忙,谢谢!

------解决方案--------------------
按日期排序 取第一个,在反着排序,取第一个,合并
------解决方案--------------------
SQL code
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)
更正一下
------解决方案--------------------
SQL code

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