mysql—从两个没有关系的表中返回相似和不同行的查询

von4xj4u 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(298)

我有两个包含多列的表，但我只关心两列（profile1和profile2）。两个表之间都没有关系。我想看看
profile1和profile2都存在于表1和表2中
profile1和profile2都存在于表1中，但不存在于表2中
profile1和profile2都存在于表2中，但不存在于表1中
表1

profile1 profile2

表2

profile1 profile2

我不确定我做得是否正确。
profile1和profile2都存在于表1和表2中

SELECT DISTINCT T2.profile1, T2.profile2
    FROM table2 as T2
WHERE  EXISTS
(
    SELECT DISTINCT T1.profile, T1.profile2
FROM table1 as T1

    WHERE T2.profcode = T1.profcode 
    AND T2.connecting_profcode = T1.connecting_profcode
);

存在于t1但不存在于t2

SELECT DISTINCT T2.profile1, T2.profile2
    FROM table2 as T2
WHERE NOT EXISTS
(
    SELECT DISTINCT DISTINCT T1.profile1, T1.profile2
FROM table1 as T1

    WHERE T2.profile1 = T1.profile1 
    AND T2.profile2 = T1.profile2
);

存在于t2，但不存在于t1

SELECT  DISTINCT T1.profile1, T1.profile2
FROM table1 as T1
WHERE NOT EXISTS
(
    SELECT DISTINCT T2.profile1, T2.profile2
    FROM table2 as T2
    WHERE T1.profile1 = T2.profile1 
    AND T1.profile2 = T2.profile2
);

sql mysql

来源：https://stackoverflow.com/questions/50278761/query-that-returns-similar-and-different-rows-from-two-tables-that-dont-have-rel

2条答案

按热度按时间

gg0vcinb1#

你想分开吗 SELECT 声明
1.profile1和profile2都存在于表1和表2中

select t1.* 
from table1 t1
where exists (select 1 from table2 where profile1 = t1.profile1 and profile2 = t1.profile2);

2.profile1和profile2都存在于表1中，但不存在于表2中

select t1.* 
from table1 t1
where not exists (select 1 from table2 where profile1 = t1.profile1 and profile2 = t1.profile2);

3.profile1和profile2都存在于表2中，但不存在于表1中

select t2.* 
from table2 t2
where not exists (select 1 from table1 where profile1 = t2.profile1 and profile2 = t2.profile2);

赞(0）回复(0）举报 2021-06-25

yhived7q2#

您也可以这样做：

Select  

* ,

ExistsInT1= Case when t2p1 is NULL AND t1p1 IS NOT NULL then 1 else 0 end,
ExistsInT2= Case when t1p1 is NULL AND t2p1 IS NOT NULL then 1 else 0 end,
ExistsInBoth= Case when t1p1 IS NOT NULL AND t2p1 IS NOT NULL then 1 else 0 end
from (

    select t1.Profile1 as T1P1, t1.Profile2 as T1P2, T2.profile1 as T2p1,
t2.profile2 as t2p2 from table1 t1 left outer join table2 t2
    on t1.profile1=t2.profile1 and t2.profile2=t1.profile2
union 
     select t1.Profile1 as T1P1, t1.Profile2 as T1P2, T2.profile1 as T2p1,
t2.profile2 as t2p2  from table1 t1 right outer join table2 t2
    on t1.profile1=t2.profile1 and t2.profile2=t1.profile2
) T

赞(0）回复(0）举报 2021-06-25

我来回答

mysql—从两个没有关系的表中返回相似和不同行的查询

2条答案

相关问题

热门标签

最新问答