問題描述

這是我的表格的簡化版本

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 查詢打印出其中一個交換機中缺失的那些.有可能這樣做嗎?請注意，所有數(shù)據(jù)都位于同一個表中.

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.

在上面提供的示例數(shù)據(jù)上，查詢應返回第 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%')

推薦答案

這會給你你想要的.它不對數(shù)據(jù)做任何假設，并會給出所有缺失的記錄.如果您想將其限制為僅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)

這篇關于如何比較同一個表中的記錄并查找缺失的記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！