34,590
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb1')is not null
drop table tb1
go
create table tb1 (id int,value int)
insert into tb1
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5
if object_id('tb2')is not null
drop table tb2
go
create table tb2 (id int,value int)
insert into tb2
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4
if object_id('tb3')is not null
drop table tb3
go
create table tb3 (id int,value int)
insert into tb3
select 1,1 union all
select 2,2 union all
select 3,3
--直接查询
select a.id,sum(a.value) as value from tb1 a join
(select tb2.id,tb2.value from tb2 where not exists(select id,value from tb3 where tb2.id=tb3.id))b
on a.id=b.id group by a.id
/*
-----------
id value
4 4
*/
--使用视图实现
create view view_select
as
select tb2.id,tb2.value from tb2 where not exists(select id,value from tb3 where tb2.id=tb3.id)
go
select a.id,sum(a.value)as value from tb1 a join view_select b
on a.id=b.id group by a.id
/*
-----------
id value
4 4
*/
我这儿测试是没问题的
你试试你那儿是不是连接关键字写的有问题