在SQL Server中使用多个NOT IN(Using multiple NOT IN in SQL Server)

编程入门行业动态更新时间:2024-10-09 05:25:59

我有两个这样的表格：

表1： City - 主键： ID_City ：

| City | ID_City | ID_State| +-----------+---------+---------+ | Chennai | 1 | 2 | | Bengaluru | 2 | 1 | | Lucknow | 3 | 3 | | . | . | . | | . | . | . |

而且，我正在使用NOT IN检查现有城市的同时将新城市添加到现有数据库。 @table看起来像这样：

表1： @table

| City | ID_State|Zip_Code| +-----------+---------+--------+ | Chennai | 2 |00001 | | Chennai | 2 |00002 | | Chennai | 2 |00003 | | Bengaluru | 1 |10011 | | Bengaluru | 1 |10012 | | Bengaluru | 1 |10013 | | Lucknow | 3 |20001 | | Mysore | 4 |50001 | | Mysore | 4 |50002 | | Mysore | 4 |50003 | | . | . | . |

预期结果：

查询应该检查已经存在的城市，并过滤掉这些，不应该添加。（这是用NOT IN完成的）

另外，如果有多个相同城市的行，则不应该添加相同城市的多个副本。例如：查询应该只添加一行City-'Mysore'而不是三行

我执行的查询：

SELECT City, id_state FROM @table WHERE City NOT IN (SELECT City FROM City WHERE City IS NOT NULL) AND id_state NOT IN (SELECT id_state FROM City WHERE id_state IS NOT NULL)

I've two tables like this:

Table 1: City - primary key: ID_City:

| City | ID_City | ID_State| +-----------+---------+---------+ | Chennai | 1 | 2 | | Bengaluru | 2 | 1 | | Lucknow | 3 | 3 | | . | . | . | | . | . | . |

And I'm adding new cities to an existing database while checking for already existing cities using NOT IN. The @table looks like this:

Table 1: @table

Expected result:

The query should check for City that are already there and filter out those and should not add. (This is done using NOT IN)

Also, it should not add multiple copies of same cities if there are multiple rows of same cities. For example: The query should add only one row of City- 'Mysore' and not three rows

Query I executed:

SELECT City, id_state FROM @table WHERE City NOT IN (SELECT City FROM City WHERE City IS NOT NULL) AND id_state NOT IN (SELECT id_state FROM City WHERE id_state IS NOT NULL)