SQL 两表关联查询问题

[t101lian]

/*
a表

A		10
B		15
C		60
D		50

b表

A		9
C		60
D		50
E		20

---得到结果
A		10		9
B		15		0
E		0		50

*/

如上，查询语句该怎么写

[二月十六]

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([id] nvarchar(21),[value] int)
Insert #a
select N'A',10 union all
select N'B',15 union all
select N'C',60 union all
select N'D',50
GO
if not object_id(N'Tempdb..#b') is null
	drop table #b
Go
Create table #b([id] nvarchar(21),[value] int)
Insert #b
select N'A',9 union all
select N'C',60 union all
select N'D',50 union all
select N'E',20
Go
--测试数据结束
SELECT  #a.id ,
        #a.value ,
        #b.value
FROM    #a
        JOIN #b ON #b.id = #a.id
                   AND #b.value <> #a.value
UNION
SELECT  #a.id ,
        #a.value ,
        0
FROM    #a
WHERE   NOT EXISTS ( SELECT *
                     FROM   #b
                     WHERE  #a.id = #b.id )
UNION
SELECT  #b.id ,
        0 ,
        #b.value
FROM    #b
WHERE   NOT EXISTS ( SELECT *
                     FROM   #a
                     WHERE  #a.id = #b.id )

[骑着蜗牛去爬山]

;with cte as(
select *,tbName='a' from #a
union all
select *,tbName='b' from #b
)
select * from(select id,v1=isnull((select value from cte c2 where c1.id=c2.id and c2.tbName='a'),0),v2=isnull((select value from cte c2 where c1.id=c2.id and c2.tbName='b'),0) from cte c1 group by id)t where v1!=v2

[zhouyuehai1978]

引用 1 楼 sinat_28984567 的回复:

不用这么复杂吧

--测试数据
if not object_id(N'Tempdb..#a') is null
    drop table #a
Go
Create table #a([id] nvarchar(21),[value] int)
Insert #a
select N'A',10 union all
select N'B',15 union all
select N'C',60 union all
select N'D',50
GO
if not object_id(N'Tempdb..#b') is null
    drop table #b
Go
Create table #b([id] nvarchar(21),[value] int)
Insert #b
select N'A',9 union all
select N'C',60 union all
select N'D',50 union all
select N'E',20
Go
--测试数据结束

SELECT ISNULL(a.id, b.id)    AS id,
       ISNULL(a.[value], 0)  AS VALUE,
       ISNULL(b.[value], 0)  AS VALUE
FROM   #a                    AS a
       FULL JOIN #b          AS b
            ON  a.id = b.id
WHERE  ISNULL(a.[value], 0) <> ISNULL(b.[value], 0)  

[二月十六]

引用 3 楼 zhouyuehai1978 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:]

不用这么复杂吧

--测试数据
if not object_id(N'Tempdb..#a') is null
    drop table #a
Go
Create table #a([id] nvarchar(21),[value] int)
Insert #a
select N'A',10 union all
select N'B',15 union all
select N'C',60 union all
select N'D',50
GO
if not object_id(N'Tempdb..#b') is null
    drop table #b
Go
Create table #b([id] nvarchar(21),[value] int)
Insert #b
select N'A',9 union all
select N'C',60 union all
select N'D',50 union all
select N'E',20
Go
--测试数据结束

SELECT ISNULL(a.id, b.id)    AS id,
       ISNULL(a.[value], 0)  AS VALUE,
       ISNULL(b.[value], 0)  AS VALUE
FROM   #a                    AS a
       FULL JOIN #b          AS b
            ON  a.id = b.id
WHERE  ISNULL(a.[value], 0) <> ISNULL(b.[value], 0)  

[/quote] 恩是不用