相同的ID，按时间排序取最大的一个值如何取？

相同的ID，按时间排序取最大的一个值怎么取？？

SQL code

    id    start_date    stop_date1      2009-01-01   2009-02-011      2009-01-02   2009-02-052      2009-01-02   null3      2009-02-05   2009-03-024      2009-02-15   2009-04-014      2009-01-31   2009-03-01

需要得到的结果
1 2009-01-02 2009-02-05
2 2009-01-02 null
3 2009-02-05 2009-03-02
4 2009-02-15 2009-04-01

类似于一张价表
有开始时间、结束时间
现在怎么筛选出某段期间内有效的价表

说明：相同的ID，取开始时间较晚的值
null代表一直使用的

[解决办法]

SQL code

declare @begin datetime ,@end datetimeset @begin='2009-01-01'set @end='2009-03-01'select * from tb twhere not exists(select * from tb where t.id=id and t.start_date&lt;start_date)and between @begin and @end datetime
[解决办法]
SQL codeselect id ,start_date=max(start_date),stop_date=max(stop_date) from tbgroup by idorder by id
[解决办法]
SQL codedeclare @begin datetime ,@end datetime 
 set @begin=&#39;2009-01-01&#39; 
 set @end=&#39;2009-03-01&#39; 
 select * from tb t 
 where not exists(select * from tb where t.id=id and t.start_date &lt;start_date) 
 and  start_date&gt;=@begin and end_date &lt;=@end 

[解决办法]
SQL codeSQL code--按某一字段分组取最大(小)值所在行的数据(爱新觉罗.毓华 2007-10-23于浙江杭州)/*数据如下：name val memoa    2   a2(a的第二个值)a    1   a1--a的第一个值a    3   a3:a的第三个值b    1   b1--b的第一个值b    3   b3:b的第三个值b    2   b2b2b2b2b    4   b4b4b    5   b5b5b5b5b5*/--创建表并插入数据：create table tb(name varchar(10),val int,memo varchar(20))insert into tb values('a',    2,   'a2(a的第二个值)')insert into tb values('a',    1,   'a1--a的第一个值')insert into tb values('a',    3,   'a3:a的第三个值')insert into tb values('b',    1,   'b1--b的第一个值')insert into tb values('b',    3,   'b3:b的第三个值')insert into tb values('b',    2,   'b2b2b2b2')insert into tb values('b',    4,   'b4b4')insert into tb values('b',    5,   'b5b5b5b5b5')go--一、按name分组取val最大的值所在行的数据。--方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name--方法2：select a.* from tb a where not exists(select 1 from tb where name = a.name and val &gt; a.val)--方法3：select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name--方法4：select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name--方法5select a.* from tb a where 1 &gt; (select count(*) from tb where name = a.name and val &gt; a.val ) order
[解决办法]
SQL codedeclare @tb table(id int,  start_date  varchar(10),   stop_date varchar(10))insert @tb select 1 ,     '2009-01-01' ,  '2009-02-01'insert @tb select 1 ,     '2009-01-02' ,  '2009-02-05'insert @tb select 2 ,     '2009-01-02' ,  nullinsert @tb select 3 ,     '2009-02-05' ,  '2009-03-02'insert @tb select 4 ,     '2009-02-15' ,  '2009-04-01'insert @tb select 4 ,     '2009-01-31' ,  '2009-03-01'select id ,start_date=max(start_date),stop_date=max(stop_date) from @tbgroup by idorder by id/*id          start_date stop_date  ----------- ---------- ---------- 1           2009-01-02 2009-02-052           2009-01-02 NULL3           2009-02-05 2009-03-024           2009-02-15 2009-04-01（所影响的行数为 4 行）
------解决方案--------------------  
SQL code-- =========================================-- -----------t_mac 小编-------------   ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL  DROP TABLE tbGOCREATE TABLE tb(id int,start_date datetime,end_date datetime)goinsert tb SELECT 1    ,  '2009-01-01'  , '2009-02-01' union all select 1     , '2009-01-02'  , '2009-02-05'union all select 2    ,  '2009-01-02' ,  null union all select 3     , '2009-02-05' ,  '2009-03-02'union all select 4    ,  '2009-02-15' ,  '2009-04-01'union all select 4     , '2009-01-31' ,  '2009-03-01'goselect * from tb twhere not exists(select * from tb where t.id=id and datediff(day,t.start_date,start_date)&gt;0)go/*------------(6 行受影响)id          start_date              end_date----------- ----------------------- -----------------------1           2009-01-02 00:00:00.000 2009-02-05 00:00:00.0002           2009-01-02 00:00:00.000 NULL3           2009-02-05 00:00:00.000 2009-03-02 00:00:00.0004           2009-02-15 00:00:00.000 2009-04-01 00:00:00.000(4 行受影响)-------*/
[解决办法]
SQL codeuse Tempdbgo--&gt; --&gt;  declare @T table([id] int,[start_date] Datetime,[stop_date] Datetime)Insert @Tselect 1,'2009-01-01','2009-02-01' union allselect 1,'2009-01-02','2009-02-05' union allselect 2,'2009-01-02',null union allselect 3,'2009-02-05','2009-03-02' union allselect 4,'2009-02-15','2009-04-01' union allselect 4,'2009-01-31','2009-03-01'declare @StartDate datetime,@EndDate datetimeselect @StartDate='2009-03-05',@EndDate='2009-04-01'--段Select     * from     @T twhere     [start_date]&lt;=@EndDate and isnull([stop_date],getdate())&gt;@StartDateand     not exists(select 1 from @T where [id]=t.[id] and isnull([stop_date],getdate())&gt;isnull(t.[stop_date],getdate()))
[解决办法]
SQL codeselect *from (select tb.*,count(*) over (partition by id order by start_date desc) as cou       from tb) ttwhere tt.cou=1;

相同的ID按时间排序取最大的一个值如

热点推荐