select a.姓名,a.数学,a.语文,a.住址,case when a.数学=b.数学 and a.语文=b.语文 then 'yes' else 'no' end as 一致性
from a left join b on a.姓名=b.姓名
union all
select b.姓名,b.数学,b.语文,'-' as 住址,case when a.数学=b.数学 and a.语文=b.语文 then 'yes' else 'no' end as 一致性
from b left join a on a.姓名=b.姓名
from a left join b on a.姓名=b.姓名
union all
select b.姓名,b.数学,b.语文,'-' as 住址,case when a.数学=b.数学 and a.语文=b.语文 then 'yes' else 'no' end as 一致性
from b left join a on a.姓名=b.姓名
a.*,[住址]=isnull(b.住址,'-'),[一致性]=case when b.住址 is null then 'NO' else 'Yes' end
from
a
left join
b on a.姓名=b.姓名
from (
select a.姓名,a.数学,a.语文,a.住址,case when a.数学=b.数学 and a.语文=b.语文 then 'yes' else 'no' end as 一致性,0 as type
from a left join b on a.姓名=b.姓名
union all
select b.姓名,b.数学,b.语文,'-' as 住址,case when a.数学=b.数学 and a.语文=b.语文 then 'yes' else 'no' end as 一致性 ,1 as type
from b left join a on a.姓名=b.姓名
) as t
order by 姓名,type
CREATE TABLE B (xm nvarchar(10),c1 int,c2 int)INSERT INTO A
SELECT
'王' , 65 , 86 , '北京'
UNION ALL SELECT
'李' , 65 , 86 , '北京'INSERT INTO B
SELECT
'王' , 65 , 86
UNION ALL SELECT
'李' , 65 , 85
GOSELECT a.*,CASE WHEN t.xm IS NULL THEN 'No' ELSE 'Yes' END as r
FROM A LEFT JOIN
(
SELECT *,NULL as C3
FROM B
)t
ON a.xm=t.xm
AND a.c1=t.c1
AND a.c2=t.c2
UNION ALL
SELECT t.* ,CASE WHEN a.xm IS NULL THEN 'No' ELSE 'Yes' END as r
FROM (
SELECT *,CAST('-' as nvarchar(30)) as C3
FROM B
)t
LEFT JOIN A
ON a.xm=t.xm
AND a.c1=t.c1
AND a.c2=t.c2
ORDER BY a.xm,a.c3
/*
xm c1 c2 c3 r
---------- ----------- ----------- ------------------------------ ----
王 65 86 - Yes
王 65 86 北京 Yes
李 65 85 - No
李 65 86 北京 No*/
一致性= CASE WHEN (A.数学 = B.数学 AND A.语文=B.语文)ession THEN 'YES' ELSE 'NO' END
FROM A FULL OUTER JOIN B ON(A.姓名=B.姓名)
你看看啊~~~~~~~
DECLARE @b TABLE(name VARCHAR(10),sx INT,yy INT)INSERT @a SELECT 'aa',65,65,'北京'
UNION ALL SELECT 'bb',65,86,'北京'INSERT @b SELECT 'aa',65,88
UNION ALL SELECT 'bb',65,86SELECT * FROM @a
SELECT * FROM @b
;WITH fc
AS
(
SELECT *,frm=0,v=0 FROM @a
UNION ALL
SELECT b.*,zz='-',frm=1,
v=CASE WHEN CHECKSUM(a.sx,a.yy)!=CHECKSUM(b.sx,b.yy) THEN 0 ELSE 1 END
FROM @b b
INNER JOIN @a a
ON a.name=b.name
)
SELECT a.name,a.sx,a.yy,a.zz,CASE WHEN mv=1 THEN 'yes' ELSE 'no' END FROM fc a
INNER JOIN
(SELECT name,MAX(v) mv FROM fc GROUP BY name) b
ON a.name=b.name
ORDER BY a.name,a.frm
DECLARE @b TABLE(name VARCHAR(10),sx INT,yy INT)INSERT @a SELECT 'aa',65,65,'北京'
UNION ALL SELECT 'bb',65,86,'北京'INSERT @b SELECT 'aa',65,88
UNION ALL SELECT 'bb',65,86
;WITH fc
AS
(
SELECT *,frm=0,v='' FROM @a
UNION ALL
SELECT b.*,zz='-',frm=1,
v=CASE WHEN CHECKSUM(a.sx,a.yy)!=CHECKSUM(b.sx,b.yy) THEN 'no' ELSE 'yes' END
FROM @b b
INNER JOIN @a a
ON a.name=b.name
)
SELECT a.name,a.sx,a.yy,a.zz,mv FROM fc a
INNER JOIN
(SELECT name,MAX(v) mv FROM fc GROUP BY name) b
ON a.name=b.name
ORDER BY a.name,a.frm