我有一个我正在选择的表,它从登录中记录“签名”。
表就像这样:
| 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)构建未签名项目列表非常简单:
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方式。
I have a table I'm selecting from, which logs "Signatures" from logins.
The table is like so:
| 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 | asdfasdSo 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 WHERE (Signed = 0 And UserId = '12') OR UserId IN (76,77,78)This works, but in the example will select record 100 and 101.
What I would like is to only select record 100 for Bob, because I have a match for 101 because of "GROUP: 2" (Which will always be unsigned), however "Bob" has signed record 101 already, hence doesn't need it in his list.
What I can't figure out if how to put that condition into the where clause, I could do it in C# once I have the dataset but would prefer a pure SQL way if possible.
最满意答案
我想你只需要一个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);I think you just need a NOT EXISTS clause?
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);更多推荐
发布评论