27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT
ID,
Name,
Time,
IP
FROM
Table
WHERE
time>='2012-01-04 00:00:00' and time<='2012-01-10 23:59:59'
SELECT ID,Name,Time,IP,
case when
exists(select 1 TABLE where Time<='2012-01-03 23:59:59' and IP=t.IP)
then 'Yes'
else 'No'
end as oldip
FROM Table
WHERE time>='2012-01-04 00:00:00' and time<='2012-01-10 23:59:59'
SELECT
ID,
Name,
Time,
IP,
case
when
IP in (select IPfrom TABLE where Time<='2012-01-03 23:59:59' group by IP)
then 'Yes'
else
'No'
end as oldip
FROM
Table
WHERE
time>='2012-01-04 00:00:00' and time<='2012-01-10 23:59:59'
use tempdb;
/*
create table A
(
ID int not null,
Name nvarchar(10) not null,
[Time] date not null,
IP nvarchar(20) not null
);
insert into A values
(1,'a','2012-01-01','192.168.1.1'),
(2,'b','2012-01-01','192.168.1.2'),
(3,'a','2012-01-04','192.168.1.1'),
(4,'c','2012-01-10','192.168.1.3');
*/
select
B.ID,
B.Name,
B.[Time],
B.IP
from A AS B
where B.IP in
(
select A.IP from A
where A.[Time] < '2012-01-04'
)
and B.[Time] between '2012-01-04' and '2012-01-10';