22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #A
(indexA INT,
indexB INT,
number INT
)
CREATE TABLE #B
(indexA INT,
indexB INT,
number INT
)
INSERT #A (indexA,
indexB,number)
SELECT 1,11,123 UNION ALL
SELECT 1,12,234 UNION ALL
SELECT 1,13,345 UNION ALL
SELECT 2,21,123 UNION ALL
SELECT 2,22,234 UNION ALL
SELECT 3,23,123
INSERT #B (indexA,
indexB,number)
SELECT 1,12,230 UNION ALL
SELECT 1,13,12 UNION ALL
SELECT 2,21,50 UNION ALL
SELECT 2,22,0
SELECT * FROM #B
UNION
SELECT a.* FROM
#A a JOIN (
SELECT a.indexA,a.indexB FROM #B b
JOIN #A a ON b.indexA=a.indexA
EXCEPT
SELECT indexA,indexB FROM #B
)e ON a.indexB=e.indexB
DROP TABLE #A
DROP TABLE #B
create table 表A
(indexA int,indexB int,number int)
insert into 表A
select 1,11,123 union all
select 1,12,234 union all
select 1,13,345 union all
select 2,21,123 union all
select 2,22,234 union all
select 3,23,123
create table 表B
(indexA int,indexB int,number int)
insert into 表B
select 1,12,230 union all
select 1,13,12 union all
select 2,21,50 union all
select 2,22,0
select indexA,indexB,number from 表B
union all
select a.indexA,a.indexB,a.number
from 表A a
left join 表B b on a.indexA=b.indexA and a.indexB=b.indexB
where b.indexA is null and b.indexB is null
and a.indexA in (select distinct indexA from 表B)
order by indexA,indexB
/*
indexA indexB number
----------- ----------- -----------
1 11 123
1 12 230
1 13 12
2 21 50
2 22 0
(5 row(s) affected)
*/
create table A(indexA int, indexB int, number int)
insert into a
select 1 ,11 ,123 union all
select 1 ,12 ,234 union all
select 1 ,13 ,345 union all
select 2 ,21 ,123 union all
select 2 ,22 ,234 union all
select 3 ,23 ,123
create table B(indexA int,indexB int,number int)
insert into B
select 1 ,12 ,230 union all
select 1 ,13 ,12 union all
select 2 ,21 ,50 union all
select 2 ,22 ,0
go
--开始查询
select a.indexA, a.indexB ,case when
(select count(1) from b where indexB=a.indexB)>0 then
(select number from b where indexB=a.indexB) else a.number end
number from a where exists (select 1 from b where indexA=a.indexA)
---查询结果
/*
indexA indexB number
----------- ----------- -----------
1 11 123
1 12 230
1 13 12
2 21 50
2 22 0
(5 行受影响)
*/
--drop table a,B
create table A(indexA int, indexB int, number int)
insert into a
select 1 ,11 ,123 union all
select 1 ,12 ,234 union all
select 1 ,13 ,345 union all
select 2 ,21 ,123 union all
select 2 ,22 ,234 union all
select 3 ,23 ,123
create table B(indexA int,indexB int,number int)
insert into B
select 1 ,12 ,230 union all
select 1 ,13 ,12 union all
select 2 ,21 ,50 union all
select 2 ,22 ,0
go
select t.indexA ,t.indexB,ISNULL(b.number,a.number) number
from
(
select distinct indexA from B
)tt
inner join
(
select indexA , indexB from a
union
select indexA , indexB from B
)t
on tt.indexA = t.indexA
left join B
on b.indexA = t.indexA and b.indexB = t.indexB
left join a
on a.indexA = t.indexA and a.indexB = t.indexB
/*
indexA indexB number
1 11 123
1 12 230
1 13 12
2 21 50
2 22 0
*/