mysql - 来自三个不同表的唯一数据

Question

我有三个名为“公共(public)”、“私有(private)”和“正常”的表。私有(private)表的优先级高于普通表和公共(public)表。每个表可能有也可能没有重复数据。我的问题是如果所有表都有相同的数据，则它应该仅从高优先级表返回，而不是从所有表返回。例如

PRIVATE TABLE
==============================
id           |  Mobileno
==============================
1            | 9999999999
------------------------------
2            | 8888888888
------------------------------


NORMAL TABLE
==============================
id           |  Mobileno
==============================
1            | 9999999988
------------------------------
2            | 8888888888
------------------------------


PUBLIC TABLE
==============================
id           |  Mobileno
==============================
1            | 9999999977
------------------------------
2            | 8888888888
------------------------------

我想要这样的结果

==============================
 Mobileno      |  type
==============================
 9999999999    | private
------------------------------
 8888888888    | private
------------------------------
 9999999988    | normal
------------------------------
 9999999977    | public

Answer 1

尝试这个查询:

select t1.Mobileno,
case when t1.Mobileno in (select Mobileno from PRIVATE) then 'PRIVATE' 
when t1.Mobileno not in (select Mobileno from PRIVATE) 
and t1.Mobileno in (select Mobileno from NORMAL) then 'NORMAL'
else 'PUBLIC' end as type
from
(select * from PRIVATE
union
select * from NORMAL
union
select * from PUBLIC)t1;

SQL Fiddle