如何比较同一个表中的记录并查找缺失的记录

2021-09-14 00:00:00 sql unique sql-server

这是我的表格的简化版本

Here is a simplified version of my table

Name      Vlan
Switch 1    1
Switch 1    2
Switch 1    3
Switch 2    1
Switch 2    2

我想将属于交换机 1 的所有 vlan 与属于交换机 2 的所有 vlan 进行比较，并使用 SQL 查询打印出其中一个交换机中缺失的那些.有可能这样做吗?请注意，所有数据都位于同一个表中.

I want to compare all vlans belonging to switch 1 with all vlans belonging to switch 2 and print out the missing ones in one of the switches using SQL query. Is it possible to do so? Note all data resides inside the same table.

在上面提供的示例数据上，查询应返回第 3 行

On the example data provided above, the query should return Row 3

Switch 1,  3

这是我之前尝试过的查询(我的要求比查询中的简化版本有更多的条件):

Here is the query I tried earlier (my requirement has few more conditions than the simplified version in my query):

Select Vlans.VLANID From VLANS
 JOIN Nodes ON 
VLANS.NodeId = Nodes.NodeID
Where Nodes.sysName LIKE 'SSW010%' and Vlans.VlanID NOT In
(Select Vlans.VLANID AS Vlan2 From VLANS
 JOIN Nodes ON 
VLANS.NodeId = Nodes.NodeID
Where Nodes.sysName LIKE 'SSW001%')

推荐答案

这会给你你想要的.它不对数据做任何假设，并会给出所有缺失的记录.如果您想将其限制为仅Switch 1"，请将其添加到 WHERE 子句中.

This will give you what you're after. It doesn't make any assumptions about the data and will give all missing records. If you want to limit it to just 'Switch 1' then add this to the WHERE clause.

SELECT
  t1.Name,
  t1.Vlan
FROM t t1
WHERE NOT EXISTS (SELECT 1 
                    FROM t t2
                   WHERE t2.Name <> t1.Name
                     AND t2.Vlan = t1.Vlan)

CREATE TABLE t 
(
  Name VARCHAR(10),
  Vlan INT
)


INSERT INTO t VALUES('Switch 1',1)   
INSERT INTO t VALUES('Switch 1', 2)
INSERT INTO t VALUES('Switch 1', 3)
INSERT INTO t VALUES('Switch 2', 1)
INSERT INTO t VALUES('Switch 2', 2)

相关文章