请教大家,那位有一句sql就能够解决的?现在有两个表
t1(no,col1,col2,col3,col4,t2no),t2(t2no,col1,col2,col3,col4)
两个表里面都有相同的四个字段
(col1,col2,col3,col4)
不同的是两个表里面的内容,t1是存放的明细信息,t1存放的汇总信息.现在我想找出这两个表里面,t1汇总信息和t2信息里面对(col1,col2,col3,col4)四个字段那些信息不等.
注意:四个信息都有可能为空目前我采用的方法是:--信息检查
Drop Table Tmp_Table1;
Drop Table Tmp_Table2;
Drop Table Tmp_Table3;--(1)明细
--(1.1)取汇总
Create Table Tmp_Table1 As
Select a.t2no,a.col1,a.col2,a.col3,a.col4,sum(a.money)
From t1 a
Group by a.t2no,a.col1,a.col2,a.col3,a.col
Order by a.t2no,a.col1,a.col2,a.col3,a.col
;
Update Tmp_Table1 Set t2no=' ' Where t2no is null;
Update Tmp_Table1 Set col1=' ' Where col1 is null;
Update Tmp_Table1 Set col2=' ' Where col2 is null;
Update Tmp_Table1 Set col3=' ' Where col3 is null;
Update Tmp_Table1 Set col4=' ' Where col4 is null;
--(1.2)取汇总
Create Table Tmp_Table2 As
Select a.t2no,a.col1,a.col2,a.col3,a.col4,a.money
From t1 a
Group by a.t2no,a.col1,a.col2,a.col3,a.col
Order by a.t2no,a.col1,a.col2,a.col3,a.col
;
Update Tmp_Table2 Set t2no=' ' Where t2no is null;
Update Tmp_Table2 Set col1=' ' Where col1 is null;
Update Tmp_Table2 Set col2=' ' Where col2 is null;
Update Tmp_Table2 Set col3=' ' Where col3 is null;
Update Tmp_Table2 Set col4=' ' Where col4 is null;
--(1.3) 比较是否正确
Create Table Tmp_Table3 As
Select a.*,
b.t2no t2nob,b.col1 col1b,b.col2 col2b,b.col3 col3b,b.col4 col4b,b.money moneyb
From Tmp_Table1 a Full OUTER JOIN Tmp_Table2 b
On a.t2no=b.t2nob and a.col1=b.col1b and a.col2=b.col2b
and a.col3=b.col3b and a.col4=b.col3b;
;
--外连接可能导致不能匹配的记录为空
Update Tmp_Table3 Set t2no=t2nob Where t2no is null;
Update Tmp_Table3 Set col2=col2b Where col2 is null;
Update Tmp_Table3 Set col3=col3b Where col3 is null;
Update Tmp_Table3 Set col4=col4b Where col4 is null;
Update Tmp_Table3 Set money=0 Where money is null;
Update Tmp_Table3 Set moneyb=0 Where moneyb is null;Delete From Tmp_Table3 Where money=moneyb;Commit;--找出不匹配的数据
select * from Tmp_Table3;
t1(no,col1,col2,col3,col4,t2no),t2(t2no,col1,col2,col3,col4)
两个表里面都有相同的四个字段
(col1,col2,col3,col4)
不同的是两个表里面的内容,t1是存放的明细信息,t1存放的汇总信息.现在我想找出这两个表里面,t1汇总信息和t2信息里面对(col1,col2,col3,col4)四个字段那些信息不等.
注意:四个信息都有可能为空目前我采用的方法是:--信息检查
Drop Table Tmp_Table1;
Drop Table Tmp_Table2;
Drop Table Tmp_Table3;--(1)明细
--(1.1)取汇总
Create Table Tmp_Table1 As
Select a.t2no,a.col1,a.col2,a.col3,a.col4,sum(a.money)
From t1 a
Group by a.t2no,a.col1,a.col2,a.col3,a.col
Order by a.t2no,a.col1,a.col2,a.col3,a.col
;
Update Tmp_Table1 Set t2no=' ' Where t2no is null;
Update Tmp_Table1 Set col1=' ' Where col1 is null;
Update Tmp_Table1 Set col2=' ' Where col2 is null;
Update Tmp_Table1 Set col3=' ' Where col3 is null;
Update Tmp_Table1 Set col4=' ' Where col4 is null;
--(1.2)取汇总
Create Table Tmp_Table2 As
Select a.t2no,a.col1,a.col2,a.col3,a.col4,a.money
From t1 a
Group by a.t2no,a.col1,a.col2,a.col3,a.col
Order by a.t2no,a.col1,a.col2,a.col3,a.col
;
Update Tmp_Table2 Set t2no=' ' Where t2no is null;
Update Tmp_Table2 Set col1=' ' Where col1 is null;
Update Tmp_Table2 Set col2=' ' Where col2 is null;
Update Tmp_Table2 Set col3=' ' Where col3 is null;
Update Tmp_Table2 Set col4=' ' Where col4 is null;
--(1.3) 比较是否正确
Create Table Tmp_Table3 As
Select a.*,
b.t2no t2nob,b.col1 col1b,b.col2 col2b,b.col3 col3b,b.col4 col4b,b.money moneyb
From Tmp_Table1 a Full OUTER JOIN Tmp_Table2 b
On a.t2no=b.t2nob and a.col1=b.col1b and a.col2=b.col2b
and a.col3=b.col3b and a.col4=b.col3b;
;
--外连接可能导致不能匹配的记录为空
Update Tmp_Table3 Set t2no=t2nob Where t2no is null;
Update Tmp_Table3 Set col2=col2b Where col2 is null;
Update Tmp_Table3 Set col3=col3b Where col3 is null;
Update Tmp_Table3 Set col4=col4b Where col4 is null;
Update Tmp_Table3 Set money=0 Where money is null;
Update Tmp_Table3 Set moneyb=0 Where moneyb is null;Delete From Tmp_Table3 Where money=moneyb;Commit;--找出不匹配的数据
select * from Tmp_Table3;
select a.t2no2,nvl(a.col1,'&'),nvl(a.col2,'&'),nvl(a.col3,'&'),nvl(a.col4,'&')
,nvl(b.col1,'&'),nvl(b.col2,'&'),nvl(b.col3,'&'),nvl(b.col4,'&'),sum(a.money),sum(a.money)
from t1 a,t2 b
where t1.t2no=t2.t2no
and nvl(a.col1,'&')=nvl(b.col1,'&')
and nvl(a.col2,'&')=nvl(b.col2,'&')
and nvl(a.col3,'&')=nvl(b.col3,'&')
and nvl(a.col4,'&')=nvl(b.col4,'&')
group by nvl(a.col1,'&'),nvl(a.col2,'&'),nvl(a.col3,'&'),nvl(a.col4,'&')
,nvl(b.col1,'&'),nvl(b.col2,'&'),nvl(b.col3,'&'),nvl(b.col4,'&');