select a.终端号, 消费总额 =isnull(select sum(消费额) from 表2 b where b.终端号=a.终端号) from 表1 a order by a.终端号
create table tb1(終端號 varchar (10),終端類型 varchar(10)) Insert into tb1 select '10','學校' union all select '11','學校' union all select '12','商店' union all select '13','商店' union all select '14','學校'create table tb2(編號 varchar(10),終端號 varchar(10),消費額 numeric(10)) Insert into tb2 select '1','10','100' union all select '2','11','100' union all select '3','10','100'--刪除 drop table tb1 drop table tb2select a.終端號, 消費總額 =isnull((select sum(消費額) from tb2 b where b.終端號=a.終端號),0) from tb1 a order by a.終端號--結果 終端號 消費總額 ------------------------------ 10 200 11 100 12 0 13 0 14 0
select a.終端號,消費額=isnull(sum(b.消費額),0) from tb1 a left join tb2 b on a.終端號=b.終端號 group by a.終端號
--示例代码: declare @tb1 table (终端号 int,终端类型 varchar(20)) insert into @tb1 select 10,'学校' union all select 11,'学校' union all select 12,'商店' union all select 13,'商店' union all select 14,'学校' declare @tb2 table (编号 int,终端号 int,消费总额 int) insert into @tb2 select 1,10,100 union all select 2,11,100 union all select 3,10,100select distinct a.终端号,sum_消费总额=isnull(b.sum_消费总额,0) from @tb1 a left join (select 终端号,sum_消费总额=sum(消费总额) from @tb2 group by 终端号)b on a.终端号=b.终端号/* 终端号 sum_消费总额 ----------- ----------- 10 200 11 100 12 0 13 0 14 0(所影响的行数为 5 行) */
消费总额 =isnull(select sum(消费额) from 表2 b where b.终端号=a.终端号)
from 表1 a order by a.终端号
Insert into tb1
select '10','學校'
union all select '11','學校'
union all select '12','商店'
union all select '13','商店'
union all select '14','學校'create table tb2(編號 varchar(10),終端號 varchar(10),消費額 numeric(10))
Insert into tb2
select '1','10','100'
union all select '2','11','100'
union all select '3','10','100'--刪除
drop table tb1
drop table tb2select a.終端號,
消費總額 =isnull((select sum(消費額) from tb2 b where b.終端號=a.終端號),0)
from tb1 a order by a.終端號--結果
終端號 消費總額
------------------------------
10 200
11 100
12 0
13 0
14 0
tb1 a left join tb2 b on a.終端號=b.終端號
group by a.終端號
declare @tb1 table (终端号 int,终端类型 varchar(20))
insert into @tb1
select 10,'学校' union all
select 11,'学校' union all
select 12,'商店' union all
select 13,'商店' union all
select 14,'学校'
declare @tb2 table (编号 int,终端号 int,消费总额 int)
insert into @tb2
select 1,10,100 union all
select 2,11,100 union all
select 3,10,100select distinct a.终端号,sum_消费总额=isnull(b.sum_消费总额,0) from @tb1 a left join (select 终端号,sum_消费总额=sum(消费总额) from @tb2 group by 终端号)b on a.终端号=b.终端号/*
终端号 sum_消费总额
----------- -----------
10 200
11 100
12 0
13 0
14 0(所影响的行数为 5 行)
*/