declare @ta table(编号 int,日期 datetime,Q1 int,Q2 int,Q3 int)
insert into @ta select 100,'2006-1-1',12,23,2
insert into @ta select 102,'2006-1-1', 2, 3,12
insert into @ta select 103,'2006-1-1', 1,23,2declare @tb table(编号 int,日期 datetime,Q1 int,Q2 int,Q3 int)
insert into @tb select 100,'2006-1-1',12,23,2
insert into @tb select 102,'2006-1-1',12, 3,12
insert into @tb select 104,'2006-1-1',12, 3,12select
编号=isnull(a.编号,b.编号),
日期=isnull(a.日期,b.日期),
Q1=case
when a.编号 is not null and b.编号 is not null and a.Q1=b.Q1 then rtrim(a.Q1)
when a.编号 is not null and b.编号 is not null and a.Q1!=b.Q1 then rtrim(b.Q1)+'(不等)'
else rtrim(isnull(a.Q1,b.Q1))
end,
Q2=case
when a.编号 is not null and b.编号 is not null and a.Q2=b.Q2 then rtrim(a.Q2)
when a.编号 is not null and b.编号 is not null and a.Q2!=b.Q2 then rtrim(b.Q2)+'(不等)'
else rtrim(isnull(a.Q3,b.Q3))
end,
Q3=case
when a.编号 is null then rtrim(b.Q3)+'(不存在)'
when b.编号 is null then rtrim(a.Q3)+'(新加记录)'
when a.Q3!=b.Q3 then rtrim(b.Q3)+'(不等)'
else rtrim(a.Q3)
end
from
@ta a
full outer join
@tb b
on
a.编号=b.编号
order by
a.编号
/*
编号 日期 Q1 Q2 Q3
------- --------------- ---------- --------- -----------
100 2006-01-01 12 23 2
102 2006-01-01 12(不等) 3 12
103 2006-01-01 1 2 2(新加记录)
104 2006-01-01 12 12 12(不存在)
*/
insert into @ta select 100,'2006-1-1',12,23,2
insert into @ta select 102,'2006-1-1', 2, 3,12
insert into @ta select 103,'2006-1-1', 1,23,2declare @tb table(编号 int,日期 datetime,Q1 int,Q2 int,Q3 int)
insert into @tb select 100,'2006-1-1',12,23,2
insert into @tb select 102,'2006-1-1',12, 3,12
insert into @tb select 104,'2006-1-1',12, 3,12select
编号=isnull(a.编号,b.编号),
日期=isnull(a.日期,b.日期),
Q1=case
when a.编号 is not null and b.编号 is not null and a.Q1=b.Q1 then rtrim(a.Q1)
when a.编号 is not null and b.编号 is not null and a.Q1!=b.Q1 then rtrim(b.Q1)+'(不等)'
else rtrim(isnull(a.Q1,b.Q1))
end,
Q2=case
when a.编号 is not null and b.编号 is not null and a.Q2=b.Q2 then rtrim(a.Q2)
when a.编号 is not null and b.编号 is not null and a.Q2!=b.Q2 then rtrim(b.Q2)+'(不等)'
else rtrim(isnull(a.Q3,b.Q3))
end,
Q3=case
when a.编号 is null then rtrim(b.Q3)+'(不存在)'
when b.编号 is null then rtrim(a.Q3)+'(新加记录)'
when a.Q3!=b.Q3 then rtrim(b.Q3)+'(不等)'
else rtrim(a.Q3)
end
from
@ta a
full outer join
@tb b
on
a.编号=b.编号
order by
a.编号
/*
编号 日期 Q1 Q2 Q3
------- --------------- ---------- --------- -----------
100 2006-01-01 12 23 2
102 2006-01-01 12(不等) 3 12
103 2006-01-01 1 2 2(新加记录)
104 2006-01-01 12 12 12(不存在)
*/
insert @ta
select 100,'2006-1-1',12,23,2 union all
select 102,'2006-1-1',2,3,12 union all
select 103,'2006-1-1',1,23,2
declare @tb table(编号 int,日期 char(10),Q1 int,Q2 int,Q3 int)
insert @tb
select 100,'2006-1-1',12,23, 2 union all
select 102,'2006-1-1',12,3,12 union all
select 104,'2006-1-1',12,3, 12
select zz.*
from
(
select 编号,日期,
q1 = case when (select q1 from @ta where 编号 = a.编号)=q1 then convert(char(10),q1)
else convert(varchar(10),q1)+'(不等)' end,
q2 = case when (select q2 from @ta where 编号 = a.编号)=q2 then convert(char(10),q2)
else convert(varchar(10),q2)+'(不等)' end,
q3 = case when (select q3 from @ta where 编号 = a.编号)=q3 then convert(char(10),q3)
else convert(varchar(10),q3)+'(不等)' end,'' as dd
from @tb a
where 编号 in(select 编号 from @ta)
union all
select 编号,日期,convert(varchar(10),q1),convert(varchar(10),q2) ,convert(varchar(10),q3) ,'(不存在)'
from @tb
where 编号 not in(select 编号 from @ta)
union all
select 编号,日期,convert(varchar(10),q1),convert(varchar(10),q2) ,convert(varchar(10),q3) ,' (新加记录)'
from @ta
where 编号 not in(select 编号 from @tb))zz
order by 编号/*
编号 日期 q1 q2 q3 dd
----------- ---------- ---------------- ---------------- ---------------- -----------
100 2006-1-1 12 23 2
102 2006-1-1 12(不等) 3 12
103 2006-1-1 1 23 2 (新加记录)
104 2006-1-1 12 3 12 (不存在)
*/