id from_date vip_no
2011-03-23 12:00:00.000 0000000020
2011-03-08 20:00:00.000 0000005012
200002 2011-03-07 17:00:00.000 0000005012
200002 2011-03-07 18:00:00.000 0000007065
2011-03-08 09:00:00.000 0000009253
200002 2011-03-09 17:00:00.000 0000009253
200002 2011-03-30 17:00:00.000 0000009836
2011-03-16 09:00:00.000 0000009863
200002 2011-03-10 17:00:00.000 0000009863
200002 2011-03-15 17:00:00.000 0000011197
2011-03-02 20:00:00.000 0000016788
200002 2011-03-01 17:00:00.000 0000016788
200002 2011-03-02 17:00:00.000 0000020197
200002 2011-03-23 17:00:00.000 0000022328
200002 2011-03-23 17:00:00.000 0000024927
2011-03-06 17:00:00.000 0000025186
200002 2011-03-24 17:00:00.000 0000025186
2011-03-03 21:00:00.000 0000025528
200002 2011-03-10 20:00:00.000 0000025528原数据如上,如果vip_no对应一条数据,就取id=200002的vip_no;
如果对应2条数,就取id=200002,并且时间(from_date)要在id=null的from_date之前的vip_no想要的数据:vip_no
0000005012
0000007065
0000009836
0000009863
0000011197
0000016788
0000020197
0000022328
0000024927各位大虾帮帮小弟~~~
2011-03-23 12:00:00.000 0000000020
2011-03-08 20:00:00.000 0000005012
200002 2011-03-07 17:00:00.000 0000005012
200002 2011-03-07 18:00:00.000 0000007065
2011-03-08 09:00:00.000 0000009253
200002 2011-03-09 17:00:00.000 0000009253
200002 2011-03-30 17:00:00.000 0000009836
2011-03-16 09:00:00.000 0000009863
200002 2011-03-10 17:00:00.000 0000009863
200002 2011-03-15 17:00:00.000 0000011197
2011-03-02 20:00:00.000 0000016788
200002 2011-03-01 17:00:00.000 0000016788
200002 2011-03-02 17:00:00.000 0000020197
200002 2011-03-23 17:00:00.000 0000022328
200002 2011-03-23 17:00:00.000 0000024927
2011-03-06 17:00:00.000 0000025186
200002 2011-03-24 17:00:00.000 0000025186
2011-03-03 21:00:00.000 0000025528
200002 2011-03-10 20:00:00.000 0000025528原数据如上,如果vip_no对应一条数据,就取id=200002的vip_no;
如果对应2条数,就取id=200002,并且时间(from_date)要在id=null的from_date之前的vip_no想要的数据:vip_no
0000005012
0000007065
0000009836
0000009863
0000011197
0000016788
0000020197
0000022328
0000024927各位大虾帮帮小弟~~~
id from_date vip_no
null 2011-03-23 12:00:00.000 0000000020
null 2011-03-08 20:00:00.000 0000005012
200002 2011-03-07 17:00:00.000 0000005012
200002 2011-03-07 18:00:00.000 0000007065
null 2011-03-08 09:00:00.000 0000009253
200002 2011-03-09 17:00:00.000 0000009253
200002 2011-03-30 17:00:00.000 0000009836
null 2011-03-16 09:00:00.000 0000009863
200002 2011-03-10 17:00:00.000 0000009863
200002 2011-03-15 17:00:00.000 0000011197
null 2011-03-02 20:00:00.000 0000016788
200002 2011-03-01 17:00:00.000 0000016788
200002 2011-03-02 17:00:00.000 0000020197
200002 2011-03-23 17:00:00.000 0000022328
200002 2011-03-23 17:00:00.000 0000024927
null 2011-03-06 17:00:00.000 0000025186
200002 2011-03-24 17:00:00.000 0000025186
null 2011-03-03 21:00:00.000 0000025528
200002 2011-03-10 20:00:00.000 0000025528
select id ,from_date, vip_no from tb where id='200002'
from a
where id='2000'and form_date!<any
(
select form_date
from a
where id is null
)
---如果对应2条数,就取id=200002,并且时间(from_date要在id=null的from_date之前的vip_no
建议用游标处理,能定位到要查询的某条记录。
如果只是你说的没ID的始终不起作用的话,那就是这样了:CREATE TABLE #T_VIP
(ID int,from_date smalldatetime,vip_NO varchar(30))
SELECT CASE WHEN COUNT(vip_NO)<=1 THEN vip_NO ELSE vip_NO END
FROM #T_VIP
WHERE ID=200002 GROUP BY vip_NO
insert into tb values (null, '2011-03-23 12:00:00.000', '0000000020')
insert into tb values (null, '2011-03-08 20:00:00.000', '0000005012')
insert into tb values (200002, '2011-03-07 17:00:00.000', '0000005012')
insert into tb values (200002, '2011-03-07 18:00:00.000', '0000007065')
insert into tb values (null, '2011-03-08 09:00:00.000', '0000009253')
insert into tb values (200002, '2011-03-09 17:00:00.000', '0000009253')
insert into tb values (200002, '2011-03-30 17:00:00.000', '0000009836')
insert into tb values (null, '2011-03-16 09:00:00.000', '0000009863')
insert into tb values (200002, '2011-03-10 17:00:00.000', '0000009863')
insert into tb values (200002, '2011-03-15 17:00:00.000', '0000011197')
insert into tb values (null, '2011-03-02 20:00:00.000', '0000016788')
insert into tb values (200002, '2011-03-01 17:00:00.000', '0000016788')
insert into tb values (200002, '2011-03-02 17:00:00.000', '0000020197')
insert into tb values (200002, '2011-03-23 17:00:00.000', '0000022328')
insert into tb values (200002, '2011-03-23 17:00:00.000', '0000024927')
insert into tb values (null, '2011-03-06 17:00:00.000', '0000025186')
insert into tb values (200002, '2011-03-24 17:00:00.000', '0000025186')
insert into tb values (null, '2011-03-03 21:00:00.000', '0000025528')
insert into tb values (200002, '2011-03-10 20:00:00.000', '0000025528')
--
with cte
as (select *,min(from_date) over (partition by vip_no) min_from_date,
COUNT(*) over (partition by vip_no) cou from tb)
select id,from_date,vip_no
from cte where from_date=min_from_date and id is not null
order by vip_noid from_date vip_no
----------- ----------------------- ----------
200002 2011-03-07 17:00:00.000 0000005012
200002 2011-03-07 18:00:00.000 0000007065
200002 2011-03-30 17:00:00.000 0000009836
200002 2011-03-10 17:00:00.000 0000009863
200002 2011-03-15 17:00:00.000 0000011197
200002 2011-03-01 17:00:00.000 0000016788
200002 2011-03-02 17:00:00.000 0000020197
200002 2011-03-23 17:00:00.000 0000022328
200002 2011-03-23 17:00:00.000 0000024927