有数据表A,表B ，表结构如下.注：跟据UID获取数据列表，在表A与表B中如果存在相同MID，优先取出表A数据排除表B数据
表A数据如下
ID MId Uid RightVal
3 10 15 1
4 10 15 4
表B数据如下
ID mid roleid rightval UID
2 10 2 1 15
11 10 2 4 15
12 10 2 6 15
13 10 2 8 15
3 11 2 9 15
14 11 2 10 15
15 11 2 11 15
16 11 2 12 15
4 14 2 1 15
取出数据结果如下
mid rightval
10 1
10 4
11 10
11 11
11 12
14 1
------解决思路----------------------
是不是少了条 11 9
SELECT mid,rightval FROM A WHERE UID=15
UNION ALL
SELECT mid,rightval FROM B WHERE UID=15
AND mid NOT IN(SELECT mid FROM A WHERE UID=15)
------解决思路----------------------
DECLARE @t_TB TABLE ([ID] NVARCHAR(10),[MId] NVARCHAR(10),[Uid] NVARCHAR(10),[RightVal] NVARCHAR(10));
INSERT INTO @t_TB VALUES
('3','10','15','1'),
('4','10','15','4');
DECLARE @t_TB2 TABLE ([ID] NVARCHAR(10),[mid] NVARCHAR(10),[roleid] NVARCHAR(10),[rightval] NVARCHAR(10),[UID] NVARCHAR(10));
INSERT INTO @t_TB2 VALUES
('2','10','2','1','15'),
('11','10','2','4','15'),
('12','10','2','6','15'),
('13','10','2','8','15'),
('3','11','2','9','15'),
('14','11','2','10','15'),
('15','11','2','11','15'),
('16','11','2','12','15'),
('4','14','2','1','15');
SELECT DISTINCT A.[MId],A.RightVal
FROM @t_TB A
UNION ALL
SELECT DISTINCT B.mid,B.rightval
FROM @t_TB2 B
INNER JOIN @t_TB A ON A.UID = B.UID AND A.MID != B.mid
/*
MId RightVal
---------- ----------
10 1
10 4
11 10
11 11
11 12
11 9 -- ?
14 1
*/