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

Question

id    start_date    stop_date1      2009-01-01   2009-02-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
4      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代表一直使用的

Answer 1

select *
fom tb a
where not exists (select 1 from tb b
    where b.id=a.id and b.start_date>a.start_dat)

Answer 2

select *
from tb a
where not exists (select 1 from tb b -- 不存在
    where b.id=a.id -- 相同id
    and b.start_date>a.start_dat -- 开始时间比当前记录更晚
    )

Answer 3

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 between @begin and @end datetime

Answer 4

select id ,start_date=max(start_date),stop_date=max(stop_date) 
from tb
group by id
order by id

Answer 5

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 

Answer 6

SQL code--按某一字段分组取最大(小)值所在行的数据
(爱新觉罗.毓华 2007-10-23于浙江杭州)
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    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
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(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,min(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 , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name       val         memo                 
---------- ----------- -------------------- 
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/
--七，如果整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
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',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3: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')
goselect * , px = identity(int,1,1) into tmp from tbselect m.name,m.val,m.memo from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
  select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)drop table tb,tmp/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
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',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3: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')
goselect m.name,m.val,m.memo from
(
  select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
  select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)drop table tb/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值(2 行受影响)
*/

Answer 7

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' ,  null
insert @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 @tb
group by id
order by id
/*
id          start_date stop_date  
----------- ---------- ---------- 
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（所影响的行数为 4 行）
*/

Answer 8

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,start_date datetime,end_date datetime)
go
insert 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 t
where 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.000
2           2009-01-02 00:00:00.000 NULL
3           2009-02-05 00:00:00.000 2009-03-02 00:00:00.000
4           2009-02-15 00:00:00.000 2009-04-01 00:00:00.000(4 行受影响)
-------*/

Answer 9

本帖最后由 roy_88 于 2009-07-21 12:36:48 编辑

  

Answer 10

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,start_date varchar(10),end_date varchar(10))
go
insert 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'
go
declare @begin varchar(10) ,@end varchar(10)
set @begin='2009-01-01'
set @end='2009-05-20'
5
select * 
from(
select id,
start_date,
end_date
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 and id not in (select id from tb where end_date is null)
union all
select * from tb where end_date is null) t
order by id go
/*------------(6 行受影响)
id          start_date end_date
----------- ---------- ----------
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-------*/

Answer 11

xiugai-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,start_date varchar(10),end_date varchar(10))
go
insert 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'
go
declare @begin varchar(10) ,@end varchar(10)
set @begin='2009-01-01'
set @end='2009-05-20'
5
select * 
from(
select id,
start_date,
end_date
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 and id not in (select id from tb where end_date is null)
union all
select * from tb where end_date is null and start_date>=@begin ) t
order by id go
/*------------(6 行受影响)
id          start_date end_date
----------- ---------- ----------
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-------*/
关键是NULL的处理

Answer 12

差不多了 要加一个时间判断限制 
比如
我要取3月份整月的有效价表结果要为：id    start_date    stop_date
2      2009-01-02   null
3      2009-02-05   2009-03-02
4      2009-02-15   2009-04-01

Answer 13

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,start_date varchar(10),end_date varchar(10))
go
insert 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'
go
declare @begin varchar(10) ,@end varchar(10)
set @begin='2009-01-01'
set @end='2009-01-31'select * 
from(
select id,
start_date,
end_date
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 and id not in (select id from tb where end_date is null)
union all
select * from tb where end_date is null and start_date>=@begin ) t
order by id go
/*------------(6 行受影响)(6 行受影响)
id          start_date end_date
----------- ---------- ----------
2           2009-01-02 NULL(1 行受影响)
-------*/
一月报单

Answer 14

14楼丢数啦
ID为1 的怎么丢了呢roy的是正确的

Answer 15

价表
结束时间为NULL的，代表一直使用的
2      2009-01-02   null  没有结束时间 所以他三月份也是有效的价表

Answer 16

呵呵　你看我的参数阿　是一月　
照你的意思就是　
如果是一月的报价表　
那应该出来是
id          start_date end_date
----------- ---------- ----------
1           2009-01-02 2009-02-05
2           2009-01-02 NULL这样？

Answer 17

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,start_date varchar(10),end_date varchar(10))
go
insert 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'
godeclare @begin varchar(10) 
set @begin='2009-03-01'select * 
from(
select id,
start_date,
end_date
from tb t
where not exists(select * from tb where t.id=id and t.start_date<start_date )
and end_date>=@begin and id not in (select id from tb where end_date is null)
union all
select * from tb where end_date is null ) t
order by id go
/*------------
(6 行受影响)
id          start_date end_date
----------- ---------- ----------
2           2009-01-02 NULL
3           2009-02-05 2009-03-02
4           2009-02-15 2009-04-01(1 行受影响)
-------*/
去测试　试试　是不是这意思

Answer 18

select *
from (select tb.*,count(*) over (partition by id order by start_date desc) as cou 
      from tb) tt
where tt.cou=1
;