求教怎么能查找出两个表中相同字段的不同的值
求教如何能查找出两个表中相同字段的不同的值
rt
表1
code name employeeCode ...
001 名字1 code1
001 名字2 code2
表2
code name employeeCode ...
001 名字1 code1
001 名字3 code2
输出
name 名字2 名字3
但是我并不知道表里具体有哪些字段,两个表字段是一样的,字段的值有不一样的。这个应该怎么写呢?
------解决思路----------------------
应该是值类型没有统一,现已修改如下
rt
表1
code name employeeCode ...
001 名字1 code1
001 名字2 code2
表2
code name employeeCode ...
001 名字1 code1
001 名字3 code2
输出
name 名字2 名字3
但是我并不知道表里具体有哪些字段,两个表字段是一样的,字段的值有不一样的。这个应该怎么写呢?
------解决思路----------------------
应该是值类型没有统一,现已修改如下
USE 你原来的数据库
GO
select identity(int,1,1) id,spbh,spmch,shpgg,zjm,dw,pizhwh,shengccj,shpchd,sptm,jlgg,dbzgg,zbzgg,
jixing,leibie,yphslb,yongyfl,cctj,chuffl,shlv,sfyjm,lsshangplx,cgshangplx,pfshangplx,
dxspxl,zxbs,cgfuzr,xsqy,youxq,sfzdsp,is_zdyh,zengp,is_ybsp,yb_spbh,spbh_yb,isdj,
is_gjjbyw,is_jkyp,xianxshl,fl1,fl2,fl3,chffchf,lsxianxshl,GSP_iszy,GSP_isptyp,GSP_istljs,
GSP_isxyzp,GSP_isdbthj,GSP_iseljsyp,GSP_isyldxyp,GSP_iszzrs,GSP_isshp,GSP_isbjsp,GSP_isylylqx,
GSP_isbxbzelylqx,GSP_isxbzelylqx,GSP_isxpfslylqx GSP_iskgmdslylqx,GSP_iswsyp,GSP_ishxsj into #t1
from spkfk where spbh='10210222'
alter table #t1 add PRIMARY KEY(id)
select identity(int,1,1) id,spbh,'asas' as spmch,shpgg,zjm,dw,pizhwh,shengccj,shpchd,sptm,jlgg,dbzgg,zbzgg,
jixing,leibie,yphslb,yongyfl,cctj,chuffl,shlv,sfyjm,lsshangplx,cgshangplx,pfshangplx,
dxspxl,zxbs,cgfuzr,xsqy,youxq,sfzdsp,is_zdyh,zengp,is_ybsp,yb_spbh,spbh_yb,isdj,
is_gjjbyw,is_jkyp,xianxshl,fl1,fl2,fl3,chffchf,lsxianxshl,GSP_iszy,GSP_isptyp,GSP_istljs,
GSP_isxyzp,GSP_isdbthj,GSP_iseljsyp,GSP_isyldxyp,GSP_iszzrs,GSP_isshp,GSP_isbjsp,GSP_isylylqx,
GSP_isbxbzelylqx,GSP_isxbzelylqx,GSP_isxpfslylqx GSP_iskgmdslylqx,GSP_iswsyp,GSP_ishxsj into #t2
from spkfk where spbh='10210222'
alter table #t2 add PRIMARY KEY(id)
USE tempdb
GO
DECLARE @T1 NVARCHAR(128)
DECLARE @T2 NVARCHAR(128)
SET @T1=N'#TestTable'
SET @T2=N'#TestTable1'
DECLARE @INDEXS NVARCHAR(MAX)
SET @INDEXS=''
SELECT
@INDEXS=@INDEXS+'AND A.['+A.[name]+']=B.['+A.[name]+']'
FROM
SYS.SYSCOLUMNS A
LEFT JOIN SYS.SYSINDEXKEYS B ON A.id=B.id AND A.colid=B.colid
WHERE
OBJECT_ID(@T1)=A.id
AND B.colid IS NOT NULL
SET @INDEXS=SUBSTRING(@INDEXS,4,LEN(@INDEXS)-3)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL=''
SELECT
@SQL=@SQL+'UNION ALL SELECT'''+A.[name]+''' ''字段名'',CAST(A.['+A.[name]+']AS NVARCHAR(MAX))''值1'',CAST(B.['+A.[name]+']AS NVARCHAR(MAX))''值2''FROM['+@T1+']A INNER JOIN['+@T2+']B ON'+@INDEXS+'WHERE A.['+A.[name]+']<>B.['+A.[name]+']'
FROM
SYS.SYSCOLUMNS A
LEFT JOIN SYS.SYSINDEXKEYS B ON A.id=B.id AND A.colid=B.colid
WHERE
OBJECT_ID(@T1)=A.id
AND B.colid IS NULL
SET @SQL=SUBSTRING(@SQL,11,LEN(@SQL)-10)
EXEC SYS.SP_EXECUTESQL @SQL