我有一个基本表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代码怎么写
------解决方案--------------------
- SQL code
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 bfull join Compare con b.id=c.id ) Tgroup by id,state
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- SQL code
--借用楼上数据 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 afull join compare b on a.id = b.id