27,579
社区成员
发帖
与我相关
我的任务
分享
--需要提醒缴款的
select c.[id],c.[bm],c.[hc] from [jk] c
where not exists (select 1 from [zj] d where (d.[bm]+d.[hc])=(c.[bm]+c.[hc]))
and (((c.[bm]+c.[hc]) in (select distinct (a.[bm]+a.[hc]) from [jk] a,[jd] b
where (a.[bm]+a.[hc]=b.[bm]+b.[hc])
and dateadd(year,1,b.[xdrq])>a.[jkrq] and a.[jkje]>=2000)
and not exists (select 1 from [jk] e,[jd] f where (e.[bm]+e.[hc])=(c.[bm]+c.[hc]) and (e.[bm]+e.[hc])=(f.[bm]+F.[hc]) and e.[jkrq]>dateadd(year,2,f.[xdrq])))
or
((c.[bm]+c.[hc]) in (select distinct (i.[bm]+j.[hc]) from [jk] i,[jd] j
where (i.[bm]+i.[hc]=j.[bm]+j.[hc])
and dateadd(year,1,j.[xdrq])>i.[jkrq] and i.[jkje]<2000)
and not exists (select 1 from [jk] g,[jd] h where (g.[bm]+g.[hc])=(c.[bm]+c.[hc]) and (g.[bm]+g.[hc])=(h.[bm]+h.[hc]) and g.[jkrq]>dateadd(year,1,h.[xdrq]))))
--需要提交法院的
select c.[id],c.[bm],c.[hc] from [jk] c
where not exists (select 1 from [zj] d where (d.[bm]+d.[hc])=(c.[bm]+c.[hc]))
and (((c.[bm]+c.[hc]) in (select distinct (a.[bm]+a.[hc]) from [jk] a,[jd] b
where (a.[bm]+a.[hc]=b.[bm]+b.[hc])
and dateadd(year,1,b.[xdrq])>a.[jkrq] and a.[jkje]<2000)
and exists (select 1 from [jk] e,[jd] f where (e.[bm]+e.[hc])=(c.[bm]+c.[hc]) and (e.[bm]+e.[hc])=(f.[bm]+F.[hc]) and e.[jkrq] between dateadd(year,1,f.[xdrq]) and dateadd(year,2,f.[xdrq])))
or
((c.[bm]+c.[hc]) in (select distinct (i.[bm]+j.[hc]) from [jk] i,[jd] j
where (i.[bm]+i.[hc]=j.[bm]+j.[hc])
and dateadd(year,1,j.[xdrq])>i.[jkrq] and i.[jkje]>=2000)
and exists (select 1 from [jk] g,[jd] h where (g.[bm]+g.[hc])=(c.[bm]+c.[hc]) and (g.[bm]+g.[hc])=(h.[bm]+h.[hc]) and g.[jkrq] between dateadd(year,2,h.[xdrq]) and dateadd(year,3,h.[xdrq]) and g.[jkje]<2000)))
--> 测试数据:[缴款库]
IF OBJECT_ID('[缴款库]') IS NOT NULL DROP TABLE [缴款库]
GO
CREATE TABLE [缴款库]([id] INT,[bm] VARCHAR(1),[hc] VARCHAR(1),[jkje] INT,[jkrq] DATETIME)
INSERT [缴款库]
SELECT 1,'a','b',2100,'2010-01-12' UNION ALL
SELECT 2,'a','b',1000,'2011-01-12' UNION ALL
SELECT 3,'a','b',2100,'2012-01-12' UNION ALL
SELECT 4,'a','c',3100,'2010-01-12' UNION ALL
SELECT 5,'a','c',1000,'2011-01-12' UNION ALL
SELECT 6,'a','c',1100,'2012-01-12' UNION ALL
SELECT 7,'a','d',1200,'2010-01-12' UNION ALL
SELECT 8,'a','d',2100,'2011-01-12' UNION ALL
SELECT 9,'a','d',3200,'2012-01-12'
GO
--> 测试语句:
--列入催缴范围的
SELECT * FROM [缴款库] as a
where jkje<=2000
and exists(
select 1 from [缴款库] as b where b.[bm]=a.[bm]
and b.[hc]=a.[hc]
and b.jkje>2000
and b.[jkrq]<a.[jkrq]
and not exists(
select 1 from [缴款库] where [bm]=b.[bm]
and [hc]=b.[hc]
and ([jkrq]>a.[jkrq] or [jkrq]<a.[jkrq])
and jkje<2000
)
)
/*
id bm hc jkje jkrq
----------- ---- ---- ----------- -----------------------
2 a b 1000 2011-01-12 00:00:00.000
(1 行受影响)
*/
--提交法院的
SELECT * FROM [缴款库] as a
where jkje<=2000
and exists(
select 1 from [缴款库] as b where b.[bm]=a.[bm]
and b.[hc]=a.[hc]
and b.jkje>2000
and b.[jkrq]<a.[jkrq]
and exists(
select 1 from [缴款库] where [bm]=b.[bm]
and [hc]=b.[hc]
and ([jkrq]>a.[jkrq] or [jkrq]<a.[jkrq])
and jkje<2000
)
)
/*
id bm hc jkje jkrq
----------- ---- ---- ----------- -----------------------
5 a c 1000 2011-01-12 00:00:00.000
6 a c 1100 2012-01-12 00:00:00.000
(2 行受影响)
*/