查询未按预期返回结果 [英] Query not returning results as expected

问题描述

我有以下查询

SELECT  id AS "CLIENT CODE",name AS "CLIENT NAME",(
                            SELECT
                                count(status)
                            FROM
                                campaigns
                            WHERE
                                clientid = clients.id
                            ) AS "TOTAL CAMPAIGNS"
FROM    clients
WHERE   id IN
        (
        SELECT  clientid
        FROM    campaigns
        WHERE   status IN ('L', 'P')
        )
        AND id NOT IN
        (
        SELECT  clientid
        FROM    campaigns
        WHERE   status NOT IN ('L', 'P')
        );

现在我有一个包含所有客户的客户表和一个包含 L、C、P、?、X 值的状态列的活动表.现在，一位客户可以拥有来自广告系列的 1 行或多行.因此，换句话说，客户可以拥有多个营销活动，并将状态值分配给一个活动.L,C,P,?,X

Now I have a clients table with all clients and a campaigns table with a status column that contains values of either L,C,P,?,X. Now one client can have 1 or more rows from campaigns. So with other words a client can have more than one campaigns with status values assigned to a campaign. Of either L,C,P,?,X

我希望查询返回给我的是具有状态为 L 和 P 且不仅是 P 不仅是 L 而且 L 和 P 的活动的客户.目前查询返回具有 P 或 L 的客户或两者兼而有之.

What I want the query to return to me is clients with campaigns where the status is L and P and not only P and not only L but both L and P. At the moment the query returns client that either have P or L or both.

推荐答案

这应该确保它同时具有

WHERE   id IN 
        ( 
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('L') 
        ) 
    AND id  IN 
        ( 
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('P') 
        ) 

或者甚至尝试 带有 EXISTS 的子查询

WHERE   EXISTS
        (
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('L') 
        AND     clientid = client.id
        )    
    AND EXISTS
        (
        SELECT  clientid 
        FROM    campaigns 
        WHERE   status IN ('P') 
        AND     clientid = client.id
        ) 

这篇关于查询未按预期返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！