求一SQL语句??

fdlqcc 2003-10-20 04:10:57
现有table1,表结构如下:
sj a b
2003-10-20 9:10:11 aaaa b
2003-10-20 9:11:11 xxxx <null>
2003-10-20 9:11:30 xxxr <null>
2003-10-20 9:11:50 xxxr b
2003-10-20 9:12:11 xxxr bb
2003-10-20 9:34:11 xxxr <null>
2003-10-20 9:35:11 xxxr b
2003-10-20 9:35:45 xxxr b
2003-10-20 9:36:11 xxxr <null>
2003-10-20 10:20:45 xxxr b
2003-10-20 10:20:45 xxxr b
想得到table1中b is null的并且sj在这条b字段是空的记录5分钟之内的记录,如:
2003-10-20 9:11:11 xxxx <null> 记录的b字段是空的,
我就要得到以下几条记录
2003-10-20 9:10:11 aaaa b
2003-10-20 9:11:50 xxxr b
2003-10-20 9:12:11 xxxr bb
请问各位高手如何用一句SQL语句
得到结果??
...全文
29 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
yujohny 2003-10-20
  • 打赏
  • 举报
回复
Select A.* from table1 A inner join
(select sj from table1 where b is null) B
on abs(datediff(minute,A.sj,B.sj)) <= 5
where A.b is not null
txlicenhe 2003-10-20
  • 打赏
  • 举报
回复
测试:
create table table1 (sj datetime,a char(10),b char(10))
insert table1 select '2003-10-20 9:10:11','aaaa','b'
Union all select '2003-10-20 9:11:11','xxxx',null
Union all select '2003-10-20 9:11:30','xxxr',null
Union all select '2003-10-20 9:11:50','xxxr','b'
Union all select '2003-10-20 9:12:11','xxxr','bb'
Union all select '2003-10-20 9:34:11','xxxr',null
Union all select '2003-10-20 9:35:11','xxxr','b'
Union all select '2003-10-20 9:35:45','xxxr','b'
Union all select '2003-10-20 9:36:11','xxxr',null
Union all select '2003-10-20 10:20:45','xxxr','b'
Union all select '2003-10-20 10:20:45','xxxr','b'

Select distinct aa.* from table1 aa
join (select sj from table1 where b is null) bb
on abs(datediff(minute,aa.sj,bb.sj)) <= 5
where aa.b is not null


sj a b
------------------------------------------------------ ---------- ----------
2003-10-20 09:10:11.000 aaaa b
2003-10-20 09:11:50.000 xxxr b
2003-10-20 09:12:11.000 xxxr bb
2003-10-20 09:35:11.000 xxxr b
2003-10-20 09:35:45.000 xxxr b

(所影响的行数为 5 行)


txlicenhe 2003-10-20
  • 打赏
  • 举报
回复
try:
Select aa.* from table1 aa
join (select sj from table1 where b is null) bb
on abs(datediff(minute,aa.sj,bb.sj)) <= 5
where aa.b is not null


22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧