Where if there's a match

本文关键字：match if there Where | 更新日期: 2023-09-27 18:12:21

我有一个表，我从中选择，从登录记录"签名"。

表是这样的:

| Int| VARCHAR  | Guid   | Bit    | Int |  NVARCHAR(MAX)
| ID | UserName | UserId | Signed | Rec | additional info etc...
| -- | -------- | ------ | ------ | --- | ---------------------
| 1  | Bob      | 12     | 0      | 100 | sasdd
| 2  | Steve    | 14     | 1      | 100 | asretg
| 3  | GROUP: 2 | 76     | 0      | 101 | This is a group of logins
| 4  | Bob      | 12     | 1      | 101 | asdfasd

第5列是被签名的目标ID

为一个特定的用户(Bob)构建一个unsigned项目的列表非常简单:

SELECT Rec FROM tbl_Sigs WHERE Signed = 0 And UserId = '12'

现在，我已经将登录组添加到该列表中，如项目3 -在本例中，组中有两个登录，并且我能够在代码中从登录中提取它有权访问的组，因此语句变为:

SELECT Rec FROM tbl_Sigs WHERE (Signed = 0 And UserId = '12')
    OR UserId IN (76,77,78)

这可以工作，但在示例中将选择记录100和101。

我想为Bob只选择记录100，因为我有一个匹配的101，因为"GROUP: 2"(它总是无符号的)，但是"Bob"已经签署了记录101，因此不需要它在他的列表中。

我无法弄清楚如果如何将该条件放入where子句，我可以在c#中完成，一旦我有数据集，但如果可能的话，我更喜欢纯SQL方式。

Where if there's a match

我认为你只需要一个NOT EXISTS子句?

DECLARE @tbl_Sigs TABLE (
    ID INT,
    UserName VARCHAR(50),
    UserId INT, --Not a GUID!
    Signed BIT,
    Rec INT,
    AdditionalInfo VARCHAR(MAX));
INSERT INTO @tbl_Sigs VALUES (1, 'Bob', 12, 0, 100, 'sasdd');
INSERT INTO @tbl_Sigs VALUES (2, 'Steve', 14, 1, 100, 'asretg');
INSERT INTO @tbl_Sigs VALUES (3, 'GROUP: 2', 76, 0, 101, 'This is a group of logins');
INSERT INTO @tbl_Sigs VALUES (4, 'Bob', 12, 1, 101, 'asdfasd');
--So column 5 is the target ID to which it's been signed.
--To build a list of unsigned items for a specific user (Bob) was pretty straight forward:
SELECT Rec FROM @tbl_Sigs WHERE Signed = 0 And UserId = '12';
--Now, I've added login groups to this list, as in item 3 - In this example, the group has both logins in it and I'm able in code to pull from the login, which group it has access to, so the statement becomes:
SELECT Rec FROM @tbl_Sigs r1 WHERE (Signed = 0 And UserId = '12') OR UserId IN (76, 77, 78)
AND NOT EXISTS (SELECT * FROM @tbl_Sigs r2 WHERE r2.Rec = r1.Rec AND r2.UserId = '12' AND r2.Signed = 1);
相关文章：