27,579
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(I int, State int)
insert into #a
select 1, 0 union all
select 2, 0 union all
select 3, 0 union all
select 4, 0
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(I int, UserName varchar(8), Update2 datetime)
insert into #b
select 1, 'aaa', '2011-02-07 12:20:20' union all
select 2, 'aaa', '2011-01-07 12:20:20'
-- exists
select * from #a t where not exists (select 1 from #b where I=t.I and datediff(day, Update2, '2011-02-09')<30)
/*
I State
----------- -----------
2 0
3 0
4 0
*/
--> 测试数据:#a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(I int, State int)
insert into #a
select 1, 0 union all
select 2, 0 union all
select 3, 0 union all
select 4, 0
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(I int, UserName varchar(8), Update2 datetime)
insert into #b
select 1, 'aaa', '2011-02-07 12:20:20' union all
select 2, 'aaa', '2011-01-07 12:20:20'
-- exists
select * from #a t where not exists (select 1 from #b where I=t.I)
or exists (select 1 from #b where I=t.I and datediff(day, Update2, '2011-02-09') > 30)
-- left join
select a.* from #a a left join #b b on a.I=b.I
where datediff(day, isnull(b.Update2,0), '2011-02-09') > 30
/*
2
3
4
*/
SELECT A.*
FROM A
WHERE (State = 0) AND (EXISTS
(SELECT 1
FROM B
WHERE A.I = B.I AND B.username = 'AAA' AND
datediff(day, B.update2, '2011-02-09') > 30)
)
ORDER BY ID DESC
--update2在2011-02-09之前之后?
AND abs(datediff(day, B.update2, '2011-02-09')) > 30
AND datediff(dd, B.update2, '2011-02-09') > 30