不管怎么样,给个答案抛砖引玉先(只是'升''降'这个关系不用代码说明太困难):
SELECT CYear, CDw,
(CName + A.CZc + ' ' + B.CZc) AS CBz
FROM TABLE1 AS A INNER JOIN TABLE2 AS B
ON (A.CYear = B.Cear + 1
AND A.CDw = B.CDw
AND A.CName = B.CName
WHERE A.CZc <> B.CZc
SELECT CYear, CDw,
(CName + A.CZc + ' ' + B.CZc) AS CBz
FROM TABLE1 AS A INNER JOIN TABLE2 AS B
ON (A.CYear = B.Cear + 1
AND A.CDw = B.CDw
AND A.CName = B.CName
WHERE A.CZc <> B.CZc
From Tab1 as a,Tab2 as b
Where a.CName = b.CName And a.cZC < b.cZC
这样的记录:
只在一个表中出现的职工
工作单位不同的职工(CDw改变)或职称改变过的职工也就是不选没有改变的职工
如果你这只是一个公司的表,不怕麻烦可以用下面的union方法,注意多个公司就不行了
Select b.CYear,a.CDW,a.CName + a.cZC + "升" + b.cZC As cBz
From Tab1 as a,Tab2 as b
Where a.CName = b.CName And a.cZC < b.cZC
union
Select b.CYear,a.CDW,a.CName + a.cZC + "降" + b.cZC As cBz
From Tab1 as a,Tab2 as b
Where a.CName = b.CName And a.cZC > b.cZC
union
Select b.CYear,a.CDW,a.CName + a.cZC + '离开'+a.CDW
Where a.CName not in (select CName from Tab2)
union
Select b.CYear,b.CDW,a.CName + a.cZC + '进入'+b.CDW
Where b.CName not in (select CName from Tab1)
Select b.CYear,a.CDW,a.CName + a.cZC + "升" + b.cZC As cBz
From Tab1 as a,Tab2 as b
Where a.CName = b.CName AND a.CDW=b.CDW And a.cZC < b.cZC
union
Select b.CYear,a.CDW,a.CName + a.cZC + "降" + b.cZC As cBz
From Tab1 as a,Tab2 as b
Where a.CName = b.CName AND a.CDW=b.CDW And a.cZC > b.cZC
union
Select b.CYear,b.CDW,a.CName + a.cZC '从'a.CDW+ "转入" +b.CDW+'成为'+ b.cZC
As cBz
From Tab1 as a,Tab2 as b
Where a.CName = b.CName AND a.CDW<>b.CDW
union
Select b.CYear,a.CDW,a.CName + a.cZC + '离开'+a.CDW
Where a.CName not in (select CName from Tab2)
union
Select b.CYear,b.CDW,a.CName + a.cZC + '进入'+b.CDW
Where b.CName not in (select CName from Tab1)
xzou(缺齿小狼) 的“not in”很好
因为还有一些其他要处理的东西
谢谢各位了。