postgre - 根据 t2 中的数据查找 t1 中不存在的行

发布于 2025-01-11 07:59:43 字数 1069 浏览 0 评论 0原文

我有一个数据表t1，其中列出了设备和在其上定义的policy对象，以及一个架构表t2，其中包含的主列表设备上应存在的 >policy 对象。我正在尝试编写一个查询，该查询将返回设备上缺少的所有策略的列表。

t1：

名称	os	策略
sw01	ios	1000M
sw01	ios	2G
sw02	iosxr	1G
sw02	iosxr	2G

t2：

os	策略
ios	1G
ios	2G
ios	3G
iosxr	1G

使用上面的示例，查询应该返回类似以下内容：

name	policy_missing
sw01	1G

我已经尝试过有一些不同的东西，但以前从未编写过这样的查询，即一个表中的一行值的键来查找另一个表中缺少的内容。任何帮助将不胜感激，谢谢！

原文

I have a data table t1 that lists devices and policy objects defined on them, and a schema table t2 that contains the master list of policy objects that should exist on devices. I am trying to write a query that will return a list of all policy's that are missing on devices.

t1:

name	os	policy
sw01	ios	1000M
sw01	ios	2G
sw02	iosxr	1G
sw02	iosxr	2G

t2:

os	policy
ios	1G
ios	2G
ios	3G
iosxr	1G

Using the sample above, the query should return something like:

name	policy_missing
sw01	1G

I have tried a few different things, but have never written a query like this before, that keys of a row of values from one table to find what is missing on another. Any help would be appreciated, thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岁月无声 2025-01-18 07:59:43

干得好

    create table t1 (name varchar(20), os varchar(10), policy varchar(10));
    create table t2 (os varchar(10), policy varchar(10));
    insert into t1 (name, os, policy) values ('sw01', 'ios', '1000M');
    insert into t1 (name, os, policy) values ('sw01', 'ios', '2G');
    insert into t1 (name, os, policy) values ('sw02', 'iosxr', '1G');
    insert into t1 (name, os, policy) values ('sw02', 'iosxr', '2G');
    
    insert into t2 (os, policy) values ('ios', '1G');
    insert into t2 (os, policy) values ('ios', '2G');
    insert into t2 (os, policy) values ('ios', '3G');
    insert into t2 (os, policy) values ('iosxr', '1G');
    
    select distinct aa.name, t2.policy from t2 
    join t1 aa on t2.os=aa.os 
    where t2.policy not in (select bb.policy from t1 bb where aa.name=bb.name)

here you go

    create table t1 (name varchar(20), os varchar(10), policy varchar(10));
    create table t2 (os varchar(10), policy varchar(10));
    insert into t1 (name, os, policy) values ('sw01', 'ios', '1000M');
    insert into t1 (name, os, policy) values ('sw01', 'ios', '2G');
    insert into t1 (name, os, policy) values ('sw02', 'iosxr', '1G');
    insert into t1 (name, os, policy) values ('sw02', 'iosxr', '2G');
    
    insert into t2 (os, policy) values ('ios', '1G');
    insert into t2 (os, policy) values ('ios', '2G');
    insert into t2 (os, policy) values ('ios', '3G');
    insert into t2 (os, policy) values ('iosxr', '1G');
    
    select distinct aa.name, t2.policy from t2 
    join t1 aa on t2.os=aa.os 
    where t2.policy not in (select bb.policy from t1 bb where aa.name=bb.name)

回复收藏 0 原文

~没有更多了~