求有得难的SQL语句:数据结构如下:
id intno productid begintime
231487 K001 0894 2008-12-4 22:51
231488 K001 0896 2008-12-5 1:13
231489 K001 0901 2008-12-5 7:40
231491 K001 0903 2008-12-3 18:12
231492 K001 0904 NULL
231493 K001 0905 NULL
231494 K001 0906 2008-12-10 15:28
231495 K001 0908 2008-12-11 3:17
231496 K001 5028 NULL
231497 K001 5033 2008-12-11 7:44
231504 F002 0894 2008-12-5 1:13
231505 F002 0896 2008-12-5 7:40
231506 F002 0901 2008-12-5 22:49
231508 F002 0903 2008-12-6 11:28
231509 F002 0904 2008-12-10 18:45
231510 F002 0905 NULL
231512 F002 0908 2008-12-11 7:13
231513 F002 5028 2008-12-11 7:47
231667 H005 0894 2008-12-4 19:30
231668 H005 0896 2008-12-4 23:06
231669 H005 0901 2008-12-5 4:05
231671 H005 0903 2008-12-5 19:14
231672 H005 0904 NULL
231675 H005 0908 2008-12-10 13:43
231676 H005 5028 2008-12-10 12:40
231677 H005 5033 NULL
查询条件:
productid='0904' and begintime is NULL
intno相同得到下一条记录begintime>'2008-12-08'查询结果:
231494 K001 0906 2008-12-10 15:28
231675 H005 0908 2008-12-10 13:43
id intno productid begintime
231487 K001 0894 2008-12-4 22:51
231488 K001 0896 2008-12-5 1:13
231489 K001 0901 2008-12-5 7:40
231491 K001 0903 2008-12-3 18:12
231492 K001 0904 NULL
231493 K001 0905 NULL
231494 K001 0906 2008-12-10 15:28
231495 K001 0908 2008-12-11 3:17
231496 K001 5028 NULL
231497 K001 5033 2008-12-11 7:44
231504 F002 0894 2008-12-5 1:13
231505 F002 0896 2008-12-5 7:40
231506 F002 0901 2008-12-5 22:49
231508 F002 0903 2008-12-6 11:28
231509 F002 0904 2008-12-10 18:45
231510 F002 0905 NULL
231512 F002 0908 2008-12-11 7:13
231513 F002 5028 2008-12-11 7:47
231667 H005 0894 2008-12-4 19:30
231668 H005 0896 2008-12-4 23:06
231669 H005 0901 2008-12-5 4:05
231671 H005 0903 2008-12-5 19:14
231672 H005 0904 NULL
231675 H005 0908 2008-12-10 13:43
231676 H005 5028 2008-12-10 12:40
231677 H005 5033 NULL
查询条件:
productid='0904' and begintime is NULL
intno相同得到下一条记录begintime>'2008-12-08'查询结果:
231494 K001 0906 2008-12-10 15:28
231675 H005 0908 2008-12-10 13:43
(
select * from tb where begintime > '2008-12-08' and intno in (select intno from tb where productid='0904' and begintime is NULL)
) m where begintime = (select min(begintime) from
(
select * from tb where begintime > '2008-12-08' and intno in (select intno from tb where productid='0904' and begintime is NULL)
) n where n.intno = m.intno
)
insert into tb values('231487', 'K001', '0894', '2008-12-4 22:51')
insert into tb values('231488', 'K001', '0896', '2008-12-5 1:13')
insert into tb values('231489', 'K001', '0901', '2008-12-5 7:40')
insert into tb values('231491', 'K001', '0903', '2008-12-3 18:12')
insert into tb values('231492', 'K001', '0904', NULL )
insert into tb values('231493', 'K001', '0905', NULL )
insert into tb values('231494', 'K001', '0906', '2008-12-10 15:28')
insert into tb values('231495', 'K001', '0908', '2008-12-11 3:17')
insert into tb values('231496', 'K001', '5028', NULL )
insert into tb values('231497', 'K001', '5033', '2008-12-11 7:44')
insert into tb values('231504', 'F002', '0894', '2008-12-5 1:13')
insert into tb values('231505', 'F002', '0896', '2008-12-5 7:40')
insert into tb values('231506', 'F002', '0901', '2008-12-5 22:49')
insert into tb values('231508', 'F002', '0903', '2008-12-6 11:28')
insert into tb values('231509', 'F002', '0904', '2008-12-10 18:45')
insert into tb values('231510', 'F002', '0905', NULL )
insert into tb values('231512', 'F002', '0908', '2008-12-11 7:13')
insert into tb values('231513', 'F002', '5028', '2008-12-11 7:47')
insert into tb values('231667', 'H005', '0894', '2008-12-4 19:30')
insert into tb values('231668', 'H005', '0896', '2008-12-4 23:06')
insert into tb values('231669', 'H005', '0901', '2008-12-5 4:05')
insert into tb values('231671', 'H005', '0903', '2008-12-5 19:14')
insert into tb values('231672', 'H005', '0904', NULL )
insert into tb values('231675', 'H005', '0908', '2008-12-10 13:43')
insert into tb values('231676', 'H005', '5028', '2008-12-10 12:40')
insert into tb values('231677', 'H005', '5033', NULL)
go--这个按时间取,好象和你的结果有点差距
select m.* from
(
select * from tb where begintime > '2008-12-08' and intno in (select intno from tb where productid='0904' and begintime is NULL)
) m where begintime = (select min(begintime) from
(
select * from tb where begintime > '2008-12-08' and intno in (select intno from tb where productid='0904' and begintime is NULL)
) n where n.intno = m.intno
)
/*
id intno productid begintime
----------- ---------- ---------- ------------------------------------------------------
231676 H005 5028 2008-12-10 12:40:00.000
231494 K001 0906 2008-12-10 15:28:00.000(所影响的行数为 2 行)
*/--这个按ID取,结果正确
select m.* from
(
select * from tb where begintime > '2008-12-08' and intno in (select intno from tb where productid='0904' and begintime is NULL)
) m where id = (select min(id) from
(
select * from tb where begintime > '2008-12-08' and intno in (select intno from tb where productid='0904' and begintime is NULL)
) n where n.intno = m.intno
)
/*
id intno productid begintime
----------- ---------- ---------- ------------------------------------------------------
231675 H005 0908 2008-12-10 13:43:00.000
231494 K001 0906 2008-12-10 15:28:00.000(所影响的行数为 2 行)
*/drop table tb
我对下面时间改了一下你再试试看对不对
id intno productid begintime
231487 K001 0894 2008-12-14 22:51
231488 K001 0896 2008-12-15 1:13
231489 K001 0901 2008-12-15 7:40
231491 K001 0903 2008-12-13 18:12
231492 K001 0904 NULL
231493 K001 0905 NULL
231494 K001 0906 2008-12-10 15:28
231495 K001 0908 2008-12-11 3:17
231496 K001 5028 NULL
231497 K001 5033 2008-12-11 7:44
231504 F002 0894 2008-12-15 1:13
231505 F002 0896 2008-12-15 7:40
231506 F002 0901 2008-12-15 22:49
231508 F002 0903 2008-12-6 11:28
231509 F002 0904 2008-12-10 18:45
231510 F002 0905 NULL
231512 F002 0908 2008-12-11 7:13
231513 F002 5028 2008-12-11 7:47
231667 H005 0894 2008-12-14 19:30
231668 H005 0896 2008-12-14 23:06
231669 H005 0901 2008-12-5 4:05
231671 H005 0903 2008-12-15 19:14
231672 H005 0904 NULL
231675 H005 0908 2008-12-10 13:43
231676 H005 5028 2008-12-10 12:40
231677 H005 5033 NULL
查询条件:
productid='0904' and begintime is NULL
intno相同得到下一条记录begintime>'2008-12-08' 查询结果:
231494 K001 0906 2008-12-10 15:28
231675 H005 0908 2008-12-10 13:43 我要求得到是productid='0904' and begintime is NULL 而且在intno相同
在productid='0904'的下一条记录begintime>'2008-12-08' 时间大于2008-12-08
不需要得到上一条记录
231487 K001 250894 2008-12-4 22:51
231488 K001 250896 2008-12-5 1:13
231489 K001 250901 2008-12-5 7:40
231491 K001 250903 2008-12-3 18:12
231492 K001 250904 NULL
231493 K001 250905 NULL
231494 K001 250906 2008-12-10 15:28
231495 K001 250908 2008-12-11 3:17
231496 K001 265028 NULL
231497 K001 265033 2008-12-11 7:44 231504 F002 250894 2008-12-5 1:13
231505 F002 250896 2008-12-5 7:40
231506 F002 250901 2008-12-5 22:49
231508 F002 250903 2008-12-6 11:28
231509 F002 250904 2008-12-10 18:45
231510 F002 250905 NULL
231512 F002 250908 2008-12-11 7:13
231513 F002 265028 2008-12-11 7:47 231667 H005 250894 2008-12-14 19:30
231668 H005 250896 2008-12-14 23:06
231669 H005 250901 2008-12-5 4:05
231671 H005 250903 2008-12-15 19:14
231672 H005 250904 NULL
231675 H005 250908 2008-12-10 13:43
231676 H005 265028 2008-12-10 12:40
231677 H005 265033 NULL 231669 G005 250894 2008-12-10 19:30
231670 G005 250896 2008-12-11 23:06
231675 G005 250901 2008-12-5 4:05
231676 G005 250903 2008-12-5 19:14
231677 G005 250904 NULL
231679 G005 250908 NULL
231680 G005 265028 NULL
231683 G005 265033 NULL
查询条件:
productid='0904' and begintime is NULL
intno相同得到下一条记录begintime>'2008-12-08' 查询结果:
231494 K001 0906 2008-12-10 15:28
231675 H005 0908 2008-12-10 13:43
只要得到productid='0904' and begintime is NULL 往下记录并且时间大于2008-12-08
不需要得到往上一条记录请各位帮忙啊
go
create table [tb]([id] int,[intno] varchar(4),[productid] varchar(4),[begintime] datetime)
insert [tb]
select 231487,'K001','0894','2008-12-14 22:51' union all
select 231488,'K001','0896','2008-12-15 1:13' union all
select 231489,'K001','0901','2008-12-15 7:40' union all
select 231491,'K001','0903','2008-12-13 18:12' union all
select 231492,'K001','0904',null union all
select 231493,'K001','0905',null union all
select 231494,'K001','0906','2008-12-10 15:28' union all
select 231495,'K001','0908','2008-12-11 3:17' union all
select 231496,'K001','5028',null union all
select 231497,'K001','5033','2008-12-11 7:44' union all
select 231504,'F002','0894','2008-12-15 1:13' union all
select 231505,'F002','0896','2008-12-15 7:40' union all
select 231506,'F002','0901','2008-12-15 22:49' union all
select 231508,'F002','0903','2008-12-6 11:28' union all
select 231509,'F002','0904','2008-12-10 18:45' union all
select 231510,'F002','0905',null union all
select 231512,'F002','0908','2008-12-11 7:13' union all
select 231513,'F002','5028','2008-12-11 7:47' union all
select 231667,'H005','0894','2008-12-14 19:30' union all
select 231668,'H005','0896','2008-12-14 23:06' union all
select 231669,'H005','0901','2008-12-5 4:05' union all
select 231671,'H005','0903','2008-12-15 19:14' union all
select 231672,'H005','0904',null union all
select 231675,'H005','0908','2008-12-10 13:43' union all
select 231676,'H005','5028','2008-12-10 12:40' union all
select 231677,'H005','5033',null
goselect * from tb
where id in (
select min(a.id) from tb a
join (select * from [tb] where productid='0904' and begintime is null) b
on a.intno=b.intno and a.id>b.id and a.begintime>'2008-12-08'
group by a.intno
)
--测试结果:
/*
id intno productid begintime
----------- ----- --------- -----------------------
231494 K001 0906 2008-12-10 15:28:00.000
231675 H005 0908 2008-12-10 13:43:00.000(2 行受影响)
*/
szx1999的SQL语句查询结果正确,谢谢szx1999,同时也感谢dawugui 热心帮助
问题解决,马上结贴