前端之家

  1. 首页
  2. 问答

如果该组在SQL的特定列中包含值，则如何散发整个组

• 问答

以下是我拥有的三种类型的表的示例

    CREATE TABLE TestCs(
    [DefendantNumber] VARCHAR(60),[FileNumber] VARCHAR(60),[ReferralDate] datetime,[BookedFirstName] VARCHAR(60),)

        INSERT INTO TestCs VALUES ('1111','510-1','2019-01-01','Mike')
        INSERT INTO TestCs VALUES ('1111','510-2','Mike')
        INSERT INTO TestCs VALUES ('2222','510-3','2019-01-02','John')
        INSERT INTO TestCs VALUES ('3333','510-4','2019-01-04','Kelly')
        INSERT INTO TestCs VALUES ('444','510-5','Lamar')

    CREATE TABLE Testcharge(
    [FileNumber] VARCHAR(60),[ChargeDescription] VARCHAR (60)
    )

    INSERT INTO Testcharge VALUES('510-1','Mu')
    INSERT INTO Testcharge VALUES('510-1','St')
    INSERT INTO Testcharge VALUES('510-2','Bu')
    INSERT INTO Testcharge VALUES('510-2','Po')
    INSERT INTO Testcharge VALUES ('510-3','Sp')
    INSERT INTO Testcharge VALUES('510-4','Po')
    INSERT INTO Testcharge VALUES('510-5','Ra')
    INSERT INTO Testcharge VALUES('510-5','Bu')


CREATE TABLE TestEvent(
[FileNumber] VARCHAR(60),[EventCode] VARCHAR (60)
)

INSERT INTO TestEvent VALUES('510-1','TR')
INSERT INTO TestEvent VALUES('510-1','HRL')
INSERT INTO TestEvent VALUES('510-1','CSCT')
INSERT INTO TestEvent VALUES('510-2','PREL')
INSERT INTO TestEvent VALUES('510-2','CSCT')
INSERT INTO TestEvent VALUES('510-3','GJ')
INSERT INTO TestEvent VALUES('510-3','DIV')
INSERT INTO TestEvent VALUES('510-3','CSCT')
INSERT INTO TestEvent VALUES('510-4','FLW')
INSERT INTO TestEvent VALUES('510-4','CST')
INSERT INTO TestEvent VALUES('510-5','CAP')
INSERT INTO TestEvent VALUES('510-5','CSCT')

我能够使用以下查询链接这些表

SELECT cs.DefendantNumber,cs.FileNumber,cs.ReferralDate,cs.BookedFirstName,chrg.ChargeDescription,ev.EventCode,chrg.ChargeDescription 
FROM TestCs AS cs INNER JOIN Testcharge AS chrg
ON cs.FileNumber=chrg.FileNumber LEFT JOIN TestEvent AS ev 
ON ev.FileNumber=cs.FileNumber
WHERE DefendantNumber IN (SELECT DefendantNumber FROM TestCs GROUP BY DefendantNumber HAVING COUNT(*)=1)

从上面从上面的查询中获得的表中，我想确保我能够获得那些没有特定费用说明的人的FileNumber。例如，我想确保我得到没有特定类型费用的FileNumber人。例如，让FileNumber中没有“ Ra”的人ChargeDescription

我在末尾使用了以下查询

AND NOT EXISTS (SELECT 1 FROM TestCs AS cs2 WHERE cs2.FileNumber=cs.FileNumber AND chrg.ChargeDescription='Ra')

但是它没有按照我的意图进行。例如，我想排除在FileNumber列中有"Ra"的人中的ChargeDescription个。因此，我应该看不到510-5。如果该组FileNumber在ChargeDescription列中包含某个值，该如何排除整个组？

唯一应显示的值为

FileNumber  DefendantNumber
510-3   2222
510-3   2222
510-3   2222
510-3   2222
510-3   2222
510-3   2222
510-4   3333
510-4   3333
abc12345687 回答：如果该组在SQL的特定列中包含值，则如何散发整个组

您可以对此使用不存在的相关子查询。

SELECT cs.DefendantNumber,cs.FileNumber,cs.ReferralDate,cs.BookedFirstName,chrg.ChargeDescription,ev.EventCode,chrg.ChargeDescription 
FROM TestCs AS cs 
INNER JOIN Testcharge AS chrg ON cs.FileNumber = chrg.FileNumber 
LEFT JOIN TestEvent AS ev ON ev.FileNumber = cs.FileNumber
WHERE cs.DefendantNumber IN 
    (
        SELECT DefendantNumber 
        FROM TestCs 
        GROUP BY DefendantNumber 
        HAVING COUNT(*) = 1
    )
    AND NOT EXISTS 
    (
        SELECT * 
        FROM Testcharge c 
        WHERE c.FileNumber = cs.FileNumber 
            AND c.ChargeDescription = 'Ra'
    )
sqlsql-server
本文链接：https://www.f2er.com/3157576.html

大家都在问