请教个MSSQL2005数据合并的问题

[hjl13960]

我有一个基本表Base(id,state)，比较表Compare(id,sate)
---------base的数据如下--------------
1,不变
2,不变
-------------------------------------
---------Compare数据如下----------
2,新增
2,新增
3,新增
-------------------------------
我想要的结果是两个表数据合并结果如下
-------result---
1，丢失
2，不变
3, 新增
---------------------
result是已base为基础，Compare为比较对象
因为两个表都有相同的"2",所以“2”是不变的；
Compare多了一个base不存在的数据"3",所以3是“新增”；
Compare少了一个base存在的数据"1",所以1是“丢失”.
请问sql代码怎么写

[jwdream2008]

select * from (
select coalesce (c.id,b.id) as id , 
 state=(case when c.id IS null then '丢失' 
				when b.id  IS not null and  c.id is not null then b.state 
				when b.id  IS  null and c.id is not null  then  '新增' end )
  from Base  b
full  join Compare c
on b.id=c.id ) T
group by id,state

[發糞塗牆]

USE tempdb
 GO
 CREATE TABLE base
     (
       id INT ,
       [state] VARCHAR(10)
     )
 CREATE TABLE compare
     (
       id INT ,
       [state] VARCHAR(10)
     )
 
 INSERT  INTO base
         SELECT  1 ,
                 '不变'
         UNION ALL
         SELECT  2 ,
                 '不变'
 
 INSERT  INTO compare
         SELECT  2 ,
                 '新增'
         UNION ALL
         SELECT  2 ,
                 '新增'
         UNION ALL
         SELECT  3 ,
                 '新增'
 
 
 SELECT DISTINCT
         CASE WHEN b.id IS NULL THEN a.id
              WHEN a.id IS NULL THEN b.id
              ELSE a.id
         END AS id ,
         CASE WHEN b.id IS NULL THEN '丢失'
              WHEN a.id IS NULL THEN '新增'
              ELSE '不变'
         END AS [state]
 FROM    base a
         FULL JOIN compare b ON a.id = b.id

[以学习为目的]

--借用楼上数据

 CREATE TABLE base(id INT ,[state] VARCHAR(10))
 CREATE TABLE compare(id INT ,[state] VARCHAR(10))
 INSERT  INTO base
         SELECT  1 ,'不变' UNION ALL
         SELECT  2 ,'不变'
 INSERT  INTO compare
         SELECT  2 ,'新增' UNION ALL
         SELECT  2 ,'新增' UNION ALL
         SELECT  3 ,'新增'
select  id= (case
               when b.id is null
                    and a.id is not null then  a.id
               when a.id is null
                    and b.id is not null then b.id
               else   null
             end)
      state=(case

               when c.id IS null then '丢失'

               when b.id  IS not null and  c.id is not null then b.state

               when b.id  IS  null and c.id is not null  then  '新增'
               else null
               end )

from   base a

full join  compare b on a.id = b.id