22,207
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('order') is not null
drop table [order]
go
create table [order](a1 varchar(100) primary key,
a2 datetime)
go
if OBJECT_ID('kc') is not null
drop table kc
go
create table kc(k1 varchar(100) primary key,
k2 int)
go
if OBJECT_ID('list') is not null
drop table list
go
create table list(b1 varchar(100) ,
b2 varchar(100) ,
b3 int)
go
insert into [order]
select 'SA2016001' ,'2016-03-20'
union all
select 'SA2016002','2016-03-21'
union all
select 'SA2016003','2016-03-22'
go
insert into kc
select 'm001', 20
union all
select 'm002', 20
union all
select 'm003', 30
union all
select 'm004', 40
go
insert into list
select 'SA2016001', 'm001', 4
union all
select 'SA2016001', 'm002', 3
union all
select 'SA2016002', 'm001', 8
union all
select 'SA2016002', 'm002', 5
union all
select 'SA2016002', 'm003', 20
union all
select 'SA2016003', 'm001', 10
union all
select 'SA2016003', 'm004', 30
go
;with ta(a1, a2, b1, b2, b3)
as
( select *
from [order] a
join list b
on a.a1 = b.b1),
tb (a1, a2, b1, b2, b3, s)
as(select *, (select SUM(b3)
from ta b
where b.a2 <= a.a2 and a.b2 = b.b2)
from ta a
)
select *
from [order] a
where a1 not in (select a1
from tb b
join kc c
on b.b2 = c.k1
where b.s > c.k2
)