相同的ID，按时间排序取最大的一个值怎么取？？
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
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<start_date)and between @begin and @end datetime
[解决办法]
select id ,start_date=max(start_date),stop_date=max(stop_date) from tbgroup by idorder by id
[解决办法]
declare @begin datetime ,@end datetime
set @begin='2009-01-01'
set @end='2009-03-01'
select * from tb t
where not exists(select * from tb where t.id=id and t.start_date <start_date)
and start_date>=@begin and end_date <=@end
SQL 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 > 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 > (select count(*) from tb where name = a.name and val > a.val ) order
[解决办法]
declare @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 行）
------解决方案--------------------
-- =========================================-- -----------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)>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 行受影响)-------*/
[解决办法]
use Tempdbgo--> --> 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]<=@EndDate and isnull([stop_date],getdate())>@StartDateand not exists(select 1 from @T where [id]=t.[id] and isnull([stop_date],getdate())>isnull(t.[stop_date],getdate()))
[解决办法]
select *from (select tb.*,count(*) over (partition by id order by start_date desc) as cou from tb) ttwhere tt.cou=1;