sql-server - SQL Server：数据不存在时返回空白值

Question

我面临一个问题，我正在寻求您的帮助。我有一个名为 as 的表freefieldassignment，其中包含一些数据，如下所示。

+-----------------+-------------------+--------+--------+
| Carrierobjectid | freefieldobjectid | value  |objectid|
+-----------------+-------------------+--------+--------+
|  90465009       | 75509             | D-AA   |90453004|
|  90465009       | 76378             | Yellow |90453062|
|  90465009       | 87395964          | ESCORT |90453072|
+-----------------+-------------------+--------+--------+

ESCORT由于用户从他正在使用的应用程序中选择，有时可能会从表中删除具有该值的最后一个事件。

我想要做的是什么时候ESCORT被删除，返回一个空白值（或空值）

我尝试使用 编写查询NOT EXISTS，但它不起作用。

它是这样的

select carrierobjectid, 
case 
     when [freefieldassignment].[value] = 'Escort' then 'Escort'
     when NOT EXISTS (SELECT [freefieldassignment].[value] FROM [freefieldassignment]
     WHERE [freefielddefinition].[objectid]= 87409935
     AND [freefieldassignment].[carrierobjectid] = 90465009) then 'NULL'

有人可以给我一些帮助吗？将不胜感激，谢谢。

标签： sql-serverdatabasesql-server-2017not-exists

Answer 1

这是我的测试表：

CREATE TABLE dbo.freefieldassignment (
 Carrierobjectid NVARCHAR(255),
 freefieldobjectid NVARCHAR(255),
 value NVARCHAR(255),
 objectid NVARCHAR(255)
)

INSERT INTO freefieldassignment (Carrierobjectid, freefieldobjectid, value, objectid)
VALUES
    ('1','2','N','2'),
    ('1','3','N','2'),
    ('1','3','N','3'),
    ('1','3','ESCORT','3')

Ans 这是我的查询。如果没有为 group by 子句中使用的值找到 ESCORT 行，则第二个查询将生成行：

SELECT Carrierobjectid, freefieldobjectid, value, objectid  FROM freefieldassignment
WHERE Carrierobjectid = 1 AND objectid = 2  
UNION ALL
SELECT Carrierobjectid, null, null, objectid 
FROM freefieldassignment
WHERE Carrierobjectid = 1 AND objectid = 2  
GROUP BY Carrierobjectid, objectid  
HAVING MAX(CASE WHEN value = 'ESCORT' THEN 1 ELSE 0 END) = 0

结果：

Carrierobjectid | freefieldobjectid | value | objectid
1               | 2                 |   N   | 2
1               | 3                 |   N   | 2
1               | NULL              | NULL  | 2

当 ESCORT 行存在时，来自联合的第二个查询不会返回数据：

SELECT Carrierobjectid, freefieldobjectid, value, objectid  FROM freefieldassignment
WHERE Carrierobjectid = 1 AND objectid = 3
UNION ALL
SELECT Carrierobjectid, null, null, objectid 
FROM freefieldassignment
WHERE Carrierobjectid = 1 AND objectid = 3  
GROUP BY Carrierobjectid, objectid  
HAVING MAX(CASE WHEN value = 'ESCORT' THEN 1 ELSE 0 END) = 0

结果

Carrierobjectid | freefieldobjectid |   value  |    objectid
1               |   3               |   N      | 3
1               |   3               |   ESCORT | 3