從常量串列中獲取對應表中沒有行的ID-有解無憂

假設我有一個具有以下值的表 Vehicles(Id, Name)：

1 Car
2 Bike
3 Bus

和一個恒定的 ID 串列：

1, 2, 3, 4, 5

我想撰寫一個查詢，從上面的串列中回傳 Id，其中 Vehicles 表中沒有行。在上面的示例中，它應該回傳：

4, 5

但是當我向 Vehicles 表添加新行時：

4 Plane

它應該只回傳：

同樣，當我從 Vehicle 表的第一個版本中洗掉第三行 (3, Bus) 時，我的查詢應該回傳：

3, 4, 5

我嘗試使用存在運算子，但它沒有為我提供正確的結果：

select top v.Id from Vehicle v where Not Exists ( select v2.Id from Vehicle v2 where v.id = v2.id and v2.id in ( 1, 2, 3, 4, 5 ))

uj5u.com熱心網友回復：

您需要將“串列”視為資料集，然后使用EXISTS：

SELECT V.I
FROM (VALUES(1),(2),(3),(4),(5))V(I) --Presumably this would be a table (type parameter),
                                     --or a delimited string split into rows
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.YourTable YT
                  WHERE YT.YourColumn = V.I);

uj5u.com熱心網友回復：

請嘗試以下解決方案。

它使用EXCEPT集合運算子。

集合運算子 - EXCEPT 和 INTERSECT (Transact-SQL)

SQL

-- DDL and sample data population, start
DECLARE @Vehicles TABLE (ID INT PRIMARY KEY, vehicleType VARCHAR(30));
INSERT INTO @Vehicles (ID, vehicleType) VALUES
(1, 'Car'),
(2, 'Bike'),
(3, 'Bus');
-- DDL and sample data population, end

DECLARE @vehicleList VARCHAR(20) = '1, 2, 3, 4, 5'
    , @separator CHAR(1) = ',';

SELECT TRIM(value) AS missingID
FROM STRING_SPLIT(@vehicleList, @separator)
EXCEPT
SELECT ID FROM @Vehicles;

輸出

 ----------- 
| missingID |
 ----------- 
|         4 |
|         5 |
 -----------

uj5u.com熱心網友回復：

在 SQL 中，我們將值存盤在表中。因此，我們將您的串列存盤在表格中。
然后使用它很簡單，我們可以輕松找到所需的資訊。
我完全同意可以使用其他功能來解決問題。使用基本 SQL 來實作資料庫設計更加智能。它將運行得更快，更易于維護，并且可以擴展到一百萬行的表而不會出現任何問題。當我們添加第 4 種運輸方式時，我們不需要修改任何其他內容。

CREATE TABLE vehicules(
id int, name varchar(25));
INSERT INTO vehicules VALUES
(1 ,'Car'),
(2 ,'Bike'),
(3 ,'Bus');

CREATE TABLE ids (iid int)
INSERT INTO ids VALUES
(1),(2),(3),(4),(5);

CREATE VIEW unknownIds AS
SELECT iid unknown_id FROM ids 
LEFT JOIN vehicules
ON iid = id
WHERE id IS NULL;

SELECT * FROM unknownIds;

| 未知 ID |
| ---------: |
| 4 |
| 5 |

INSERT INTO vehicules VALUES (4,'Plane')

SELECT * FROM unknownIds;

| 未知 ID |
| ---------: |
| 5 |

db<>在這里擺弄

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451031.html

標籤：sql sql服务器

上一篇：如何用“總計”替換列最后一行的NULL值，同時保留“總計”替換同一列中的NULL值？

下一篇：如何在存盤程序中概括JSON？