代码之家 › 专栏 › 技术社区 › ithoughtso

如何在SQL中选择两个表之间不匹配的行?

join sql-server sql

-1

ithoughtso · 技术社区 · 7 年前

我有两张桌子t1和t2

t1

plant  country    cost
------------------------
apple  usa        1
apple  uk         1
potato sudan      3
potato india      3
potato china      3
apple  usa        2
apple  uk         2


t2

country
--------
usa
uk
egypt
sudan
india
china

我需要为t1中不存在的国家返回一张表,如下所示:

plant  country    cost
------------------------
apple  egypt      1
apple  sudan      1
apple  india      1
apple  china      1
apple  egypt      2
apple  sudan      2
apple  india      2
apple  china      2
potato usa        3
potato uk         3
potato egypt      3

select t1.plant, t2.country, t1.cost
from t1
right outer join t1 on t1.country = t2.country
where t2 is null
group by t1.plant, t2.country, t1.cost

我在堆栈溢出中查看了几个“不存在”问题,但这些响应不起作用,因为t1和t2之间的公共列比我的示例中的多。有人能给我指出正确的方向吗?或者给我一个类似问题的链接吗?

2 回复 | 直到 7 年前

1

2

Tim Biegeleisen 7 年前

我们可以尝试通过使用动态日历表来处理此问题:

WITH cte AS (
    SELECT DISTINCT t1.plant, t2.country, t1.cost
    FROM t1
    CROSS JOIN t2
)

SELECT
    a.plant,
    a.country,
    a.cost
FROM cte a
WHERE NOT EXISTS (SELECT 1 FROM t1 b
                  WHERE a.plant = b.plant AND
                        a.country = b.country AND
                        a.cost = b.cost);

Demo

2

0

GMB 7 年前

你可以用一个 CROSS JOIN 生成所有可能的组合 plants 和 countires ,然后是 NOT EXISTS 具有相关子查询以筛选出 t2 :

select plants.plant, countries.country, plants.cost
from t2 countries
cross join (distinct plant, max(cost) cost from t1 group by plant) plants
where not exists (
    select 1 from t1 where t1.country = countries.country
)

3

0

JERRY 7 年前

SELECT t.plant, t2.country, t.cost
FROM(
SELECT DISTINCT plant , cost
FROM t1)t
CROSS JOIN t2
LEFT JOIN t1 ON t1.plant = t.plant and t1.country = t2.country AND t.cost = t1.cost
WHERE t1.plant IS NULL AND t1.country IS NULL AND t1.cost is NULL