select * from meterdata t
where not exists(select 1 from meterdata
where ammeterdata=t.ammeterdata
and
readdate<t.readdate)
select * from meterdata t
where not exists(select 1 from meterdata
where ammeterdata=t.ammeterdata
and
readdate>t.readdate)
where not exists(select 1 from meterdata
where ammeterdata=t.ammeterdata
and
readdate<t.readdate)
select * from meterdata t
where not exists(select 1 from meterdata
where ammeterdata=t.ammeterdata
and
readdate>t.readdate)
declare @t table (ammeterdata varchar(3),value int,readdate datetime)
insert into @t select '001',10,'2005-07-01'
union all select '001',50,'2005-07-03'
union all select '001',60,'2005-07-28'
union all select '002',30,'2005-07-02'
union all select '002',40,'2005-07-30'
union all select '005',5,'2005-07-03'
union all select '005',32,'2005-07-05'
union all select '005',90,'2005-07-29'
--查询语句
select ammeterdata,value=min(value),readdate=min(readdate)
from @t a
group by ammeterdataselect ammeterdata,value=max(value),readdate=max(readdate)
from @t a
group by ammeterdata
--结果
ammeterdata value readdate
----------- ----------- -------------------------
001 10 2005-07-01 00:00:00.000
002 30 2005-07-02 00:00:00.000
005 5 2005-07-03 00:00:00.000(所影响的行数为 3 行)ammeterdata value readdate
----------- ----------- ------------------------
001 60 2005-07-28 00:00:00.000
002 40 2005-07-30 00:00:00.000
005 90 2005-07-29 00:00:00.000(所影响的行数为 3 行)
select *
from meterdata m
where readdate=(select min(readdate)
from meterdata
where ammeterdata=m.ammeterdata)--The Second one.select *
from meterdata m
where value=(select max(value)
from meterdata
where ammeterdata=m.ammeterdata)
where ammeterdate=a.ammeterdate and value<a.value)select ammeterdata,value,readdate from test a where not exists(select 1 from table1
where ammeterdate=a.ammeterdate and value>a.value)
where ammeterdate=a.ammeterdate and value<a.value)select ammeterdata,value,readdate from meterdata a where not exists(select 1 from meterdata
where ammeterdate=a.ammeterdate and value>a.value)