表一:t1
ID Card 成本
3 LF120-0025 0
3 LF3B121-0025/1 5
3 LF3B121-0025/2 10
表二:t2
ID Card1 card2
3 LF120-0025 LF3B121-0025/1
3 LF120-0025 LF3B121-0025/2
结果显示:
表三:t3
ID Card 成本
3 LF120-0025 15
ID Card 成本
3 LF120-0025 0
3 LF3B121-0025/1 5
3 LF3B121-0025/2 10
表二:t2
ID Card1 card2
3 LF120-0025 LF3B121-0025/1
3 LF120-0025 LF3B121-0025/2
结果显示:
表三:t3
ID Card 成本
3 LF120-0025 15
from t2 as a
left join t1 as b
on a.card = b.card2
group by ID ,Card1
from t2 as a
left join t1 as b
on a.card = b.card2
group by a.ID ,a.Card1
on t1.card=t2.card1
group by t2.id,t2.card1
from t2 as a
left join t1 as b
on a.card = b.card2
group by a.ID ,a.Card1
insert @t1 select 3, 'LF120-0025' , 0
insert @t1 select 3, 'LF3B121-0025/1', 5
insert @t1 select 3, 'LF3B121-0025/2' , 10
declare @t2 table(ID int, Card1 varchar(25), card2 varchar(20))
insert @t2 select 3 , 'LF120-0025', 'LF3B121-0025/1'
insert @t2 select 3 , 'LF120-0025', 'LF3B121-0025/2'
select t2.id,t2.card1,sum(t1.成本) from (select distinct id ,card1 from @t2) t2 inner join @t1 t1 on t2.id=t1.id group by t2.id,t2.card1
/*id card1
----------- ------------------------- ---------------------
3 LF120-0025 15.0000*/
表一:t1
ID Card 成本
3 LF120-0025 0
3 LF3B121-0025/1 5
3 LF3B121-0025/2 10 */
create table #t1
(
ID int,
Card nvarchar(30),
Account int
)
insert into #t1 select 3,'LF120-0025',0
union all select 3,'LF3B121-0025/1',5
union all select 3,'LF3B121-0025/2',10
/*
表二:t2
ID Card1 card2
3 LF120-0025 LF3B121-0025/1
3 LF120-0025 LF3B121-0025/2
*/
create table #t2
(
ID int,
Card1 nvarchar(30),
Card2 nvarchar(30)
)
insert into #t2 select 3,'LF120-0025','LF3B121-0025/1'
union all select 3,'LF120-0025','LF3B121-0025/2'select t2.ID,t2.card1,sum(t1.Account) '总成本' from #t1 t1 join #t2 t2 on t1.Card=t2.Card2 group by t2.ID,t2.card1
表一:t1
ID Card 成本
3 LF120-0025 0
3 LF3B121-0025/1 5
3 LF3B121-0025/2 10
4 3B121-0025 20
表二还是照样结果显示:
表三:t3
ID Card 成本
3 LF120-0025 15
4 3B121-0025 20
大家再帮忙看一下该如何做?
insert @t1 select 3, 'LF120-0025' , 0
insert @t1 select 3, 'LF3B121-0025/1', 5
insert @t1 select 3, 'LF3B121-0025/2' , 10
insert @t1 select 4, '3B121-0025', 20
declare @t2 table(ID int, Card1 varchar(25), card2 varchar(20))
insert @t2 select 3 , 'LF120-0025', 'LF3B121-0025/1'
insert @t2 select 3 , 'LF120-0025', 'LF3B121-0025/2'
select t1.id,isnull(t2.card1,t1.card),sum(t1. 成本) from @t1 t1 left join (select distinct id ,card1 from @t2) t2 on t1.id=t2.id group by t1.id,isnull(t2.card1,t1.card)
/*id
----------- ------------------------- ---------------------
4 3B121-0025 20.0000
3 LF120-0025 15.0000*/
if object_id('t1')is not null
drop table t1
if object_id('t2')is not null
drop table t2
create table t1 (id int,card nvarchar(20),成本 int)
insert into t1 select 3,'lf120-0025',0
union all select 3,'lf3b121-0025/1',5
union all select 3,'lf3b121-0025/2',10
create table t2(id int,card1 varchar(20),card2 nvarchar(20))
insert into t2 select 3,'lf120-0025','lf3b121-0025/1'
union all select 3,'lf120-0025','lf3b121-0025/2'
select a.id,max(a.card)card,sum(a.成本) 成本 from t1 a
left join t2 b on a.card=b.card2
group by a.id
3 lf3b121-0025/2 15