Select * from tb as a
where exists(Select * from tb where a1=a.a1 and
a.ID<>ID and abs(datediff(day,a2,a.a2))<=2)
where exists(Select * from tb where a1=a.a1 and
a.ID<>ID and abs(datediff(day,a2,a.a2))<=2)
解决方案 »
- 存储过程中 带输出参数的问题。
- 请问怎么用SQL语句实现这个查询
- Sqlserver进程信息中有13个master进程,很多个我程序中用到的进程,正常不?
- 继续昨天的问题,需要再变动,谢谢!
- 关于天网防火墙拒绝访问SQL设置
- 字段的开头包含相连的数据如何写出来
- sql server 如何做健康性检查?
- SQL Server连接问题???
- 一段if exits 语句,一直提示语法错误,请求帮助
- 请问怎么将某一列中的重复记录的一列过滤成只要第一个记录(过滤除第一条整行的记录)?
- 如何写SQL语句将小数转换成百分数,并保留两位有效数字?急!
- 查询后字段类型发生变化,int(4)变成int(10),不思其解
where exists (
select 1 from tablename
where a1=a.a1
and id<>a.id
and abs(datediff(day,a2,a,a2))<=2
)
INSERT INTO @tb
SELECT 3114, 69, '2007-05-01 00:00:00.000'
UNION ALL SELECT 2366, 69, '2007-05-01 00:00:00.000'
UNION ALL SELECT 2095, 69, '2007-07-01 00:00:00.000'
UNION ALL SELECT 2944, 81, '2007-04-20 00:00:00.000'
UNION ALL SELECT 2019, 81, '2007-04-22 00:00:00.000'
UNION ALL SELECT 3094, 81, '2007-04-23 00:00:00.000'
UNION ALL SELECT 2210, 81, '2007-06-01 00:00:00.000'SELECT * FROM @tb A WHERE (SELECT COUNT(1) FROM @tb WHERE A1 = A.A1 AND DATEDIFF(DAY, A2, A.A2) BETWEEN -2 AND 2)>1
insert @ta
select 3114, 69, '2007-05-01 00:00:00.000' union all
select 2366, 69, '2007-05-01 00:00:00.000' union all
select 2095, 69, '2007-07-01 00:00:00.000' union all
select 2944, 81, '2007-04-20 00:00:00.000' union all
select 2019, 81, '2007-04-22 00:00:00.000' union all
select 3094, 81, '2007-04-23 00:00:00.000' union all
select 2210, 81, '2007-06-01 00:00:00.000'select * from @ta a
where exists(select * from @ta where a1=a.a1 and id<>a.id and datediff(day,a2,a.a2) between 0 and 2 )
or
exists(select * from @ta where a1=a.a1 and id<>a.id and datediff(day,a.a2,a2)between 0 and 2)(所影响的行数为 7 行)id a1 a2
----------- ----------- ------------------------------------------------------
3114 69 2007-05-01 00:00:00.000
2366 69 2007-05-01 00:00:00.000
2944 81 2007-04-20 00:00:00.000
2019 81 2007-04-22 00:00:00.000
3094 81 2007-04-23 00:00:00.000(所影响的行数为 5 行)
id a1 a2
3114 69 2007-05-01 00:00:00.000
2366 69 2007-05-01 00:00:00.000
2095 69 2007-07-01 00:00:00.000
2944 81 2007-04-20 00:00:00.000
2019 81 2007-04-22 00:00:00.000
3094 81 2007-04-25 00:00:00.000
2210 81 2007-06-01 00:00:00.000找出a1相同,并且a2的日期在正负2天内相同的记录,并标注出相关的
3114 69 2007-05-01 00:00:00.000 3114,2366
2366 69 2007-05-01 00:00:00.000 3114,2366
2944 81 2007-04-20 00:00:00.000 2944,2019
2019 81 2007-04-22 00:00:00.000 2944,2019,3094
3094 81 2007-04-25 00:00:00.000 2019,3094
id a1 a2
3114 69 2007-05-01 00:00:00.000
2366 69 2007-05-01 00:00:00.000
2095 69 2007-07-01 00:00:00.000
2944 81 2007-04-20 00:00:00.000
2019 81 2007-04-22 00:00:00.000
3094 81 2007-04-25 00:00:00.000
2210 81 2007-06-01 00:00:00.000找出a1相同,并且a2的日期在正负2天内相同的记录,并标注出相关的
3114 69 2007-05-01 00:00:00.000 3114,2366
2366 69 2007-05-01 00:00:00.000 3114,2366
2944 81 2007-04-20 00:00:00.000 2944,2019
2019 81 2007-04-22 00:00:00.000 2944,2019,3094
3094 81 2007-04-25 00:00:00.000 2019,3094================================================
2019,3094对应的日期分别是22号跟25号,不在2天范围内阿!
那最后一条纪录何解?
3114 69 2007-05-01 00:00:00.000
2366 69 2007-05-01 00:00:00.000
2095 69 2007-07-01 00:00:00.000
2944 81 2007-04-20 00:00:00.000
2019 81 2007-04-22 00:00:00.000
3094 81 2007-04-24 00:00:00.000
2210 81 2007-06-01 00:00:00.000找出a1相同,并且a2的日期在正负2天内相同的记录,并标注出相关的
3114 69 2007-05-01 00:00:00.000 3114,2366
2366 69 2007-05-01 00:00:00.000 3114,2366
2944 81 2007-04-20 00:00:00.000 2944,2019
2019 81 2007-04-22 00:00:00.000 2944,2019,3094
3094 81 2007-04-24 00:00:00.000 2019,3094
笔误,改了下日期
create table tab
(
id int,
a1 int,
a2 datetime
)insert into tab select 3114, 69, '2007-05-01 00:00:00.000'
insert into tab select 2366, 69, '2007-05-01 00:00:00.000'
insert into tab select 2095, 69, '2007-07-01 00:00:00.000'
insert into tab select 2944, 81, '2007-04-20 00:00:00.000'
insert into tab select 2019, 81, '2007-04-22 00:00:00.000'
insert into tab select 3094, 81, '2007-04-24 00:00:00.000'
insert into tab select 2210, 81, '2007-06-01 00:00:00.000'--建立函数
create function f_addstr(@id int)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str = ''
select @str = @str + ',' + cast(bid as varchar) from tab2 where id = @id
return (@str)
end
go--导入临时表
select a.id,a.a1,a.a2,b.id as bid
into tab2
from tab a left join tab b on a.a1 = b.a1 and abs(datediff(day,a.a2,b.a2)) <= 2--查询
select id,a1,a2,dbo.f_addstr(id) as ida
from tab2 t1
where exists (select 1 from tab2 where id = t1.id and bid <> t1.bid)
group by id,a1,a2
order by a1,a2--结果
2366
69 2007-05-01 00:00:00.000 3114,2366 3114
69 2007-05-01 00:00:00.000 3114,2366 2944
81 2007-04-20 00:00:00.000 2944,2019 2019
81 2007-04-22 00:00:00.000 2944,2019,3094 3094
81 2007-04-24 00:00:00.000 2019,3094 --删除环境
drop table tab
drop table tab2
--语句
select id,a1,a2,STUFF(dbo.f_addstr(id),1,1,'') as ida
from tab2 t1
where exists (select 1 from tab2 where id = t1.id and bid <> t1.bid)
group by id,a1,a2
order by a1,a2