34,588
社区成员
发帖
与我相关
我的任务
分享
declare @a TABLE (NO1 varchar(10),m_money int )
declare @b TABLE (NO2 varchar(10),f_money int )
insert @a
SELECT '001',50 UNION ALL
SELECT '002',100
insert @b
SELECT '001',100 UNION ALL
SELECT '003',200
select
no= case when a.no1 is null then b.no2 else a.no1 end,
m_money=case when a.m_money is null then 0 else a.m_money end,
f_money=case when b.f_money is null then 0 else b.f_money end
from @a a
right join @b b
on a.no1=b.no2
union
select
no= case when a.no1 is null then b.no2 else a.no1 end,
m_money=case when a.m_money is null then 0 else a.m_money end,
f_money=case when b.f_money is null then 0 else b.f_money end
from @a a
left join @b b
on a.no1=b.no2
/*
no m_money f_money
001 50 100
002 100 0
003 0 200
*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
IF OBJECT_ID('a') IS NOT NULL
DROP TABLE a
GO
CREATE TABLE a(NO1 varchar(10),m_money int )
go
insert a SELECT '001',50
UNION ALL SELECT '002',100
go
IF OBJECT_ID('b') IS NOT NULL
DROP TABLE b
GO
CREATE TABLE b(NO2 varchar(10),f_money int )
go
insert b SELECT '001',100
UNION ALL SELECT '003',200
go
select NO,MAX(m_money) as m_money,MAX(f_money) as f_money
from(
select no=NO1,m_money,f_money=0 from a
union all
select no=NO2,m_money=0,f_money from b) t
group by no
go
/*------------
NO m_money f_money
---------- ----------- -----------
001 50 100
002 100 0
003 0 200
(3 行受影响)
-------*/