如何根据合并列的值过滤分组查询？

Question

这是我的查询不起作用，因为它显然违反了 HAVING 子句的规则：

SELECT
      COALESCE(Publisher.name, Affiliate.name) AS Publisher
     ,dbo.SumKeys(Item.id) AS ItemIDs
FROM
    Item
    INNER JOIN ItemAccountingStatus ON Item.item_accounting_status_id = ItemAccountingStatus.id
    INNER JOIN Affiliate ON Item.affid = Affiliate.affid
    INNER JOIN Currency AffiliateCurrency ON Affiliate.currency_id = AffiliateCurrency.id
    INNER JOIN Campaign ON Item.pid = Campaign.pid
    INNER JOIN CampaignStatus ON Campaign.campaign_status_id = CampaignStatus.id
    LEFT OUTER JOIN Publisher ON Affiliate.affid = Publisher.affid
GROUP BY
     COALESCE(Publisher.name, Affiliate.name)
    ,ItemAccountingStatus.name
    ,CampaignStatus.name    
HAVING (
    ItemAccountingStatus.name='default'
    and CampaignStatus.name='Verified'
    and Publisher='AdsMain LLC' -- THIS BREAKS THE QUERY
)

问题：有什么方法可以做到这一点吗？

Answer 1

我怀疑您是否需要having子句 - 在where子句中尝试如下

SELECT
      COALESCE(Publisher.name, Affiliate.name) AS Publisher
     ,dbo.SumKeys(Item.id) AS ItemIDs
FROM
    Item
    INNER JOIN ItemAccountingStatus ON Item.item_accounting_status_id = ItemAccountingStatus.id
    INNER JOIN Affiliate ON Item.affid = Affiliate.affid
    INNER JOIN Currency AffiliateCurrency ON Affiliate.currency_id = AffiliateCurrency.id
    INNER JOIN Campaign ON Item.pid = Campaign.pid
    INNER JOIN CampaignStatus ON Campaign.campaign_status_id = CampaignStatus.id
    LEFT OUTER JOIN Publisher ON Affiliate.affid = Publisher.affid
where 
    ItemAccountingStatus.name='default'
    and CampaignStatus.name='Verified'
    and Publisher='AdsMain LLC' 
GROUP BY
     COALESCE(Publisher.name, Affiliate.name)
    ,ItemAccountingStatus.name
    ,CampaignStatus.name    

Answer 2

...
and COALESCE(Publisher.name, Affiliate.name) ='AdsMain LLC'
...

仅在 ORDER BY 中允许使用列别名。您必须在 WHERE、GROUP BY、HAVING 等中使用表达式（生成别名列）。您已经按照预期在 GROUP BY 中使用了表达式。

有些 DBMS 允许您在其他地方引用列别名，但 SQL Server 不允许，我认为这使得发生的情况更加明显。

或者您可以使用派生表/CTE 方法并从中选择（根据您的自我回答）

按照 MSDN 上的 SELECT，“SELECT 语句的逻辑处理顺序”

...相反，因为 SELECT 子句是步骤 8，所以该子句中定义的任何列别名或派生列都不能被前面的子句引用。但是，它们可以由后续子句（例如 ORDER BY 子句）引用。请注意，语句的实际物理执行由查询处理器确定，并且顺序可能与此列表不同。

1. 来自
2. 开启
3. 加入
4. 哪里
5. 分组依据
6. 使用多维数据集或使用 ROLLUP
7. HAVING（gbn：别名不可用）
8. SELECT（gbn：此处生成的别名）
9. 独特
10. 排序依据
11. 顶部

Answer 3

SELECT * FROM
(
    SELECT
          COALESCE(Publisher.name, Affiliate.name) AS Publisher
         ,dbo.SumKeys(Item.id) AS ItemIDs
    FROM
        Item
        INNER JOIN ItemAccountingStatus ON Item.item_accounting_status_id = ItemAccountingStatus.id
        INNER JOIN Affiliate ON Item.affid = Affiliate.affid
        INNER JOIN Currency AffiliateCurrency ON Affiliate.currency_id = AffiliateCurrency.id
        INNER JOIN Campaign ON Item.pid = Campaign.pid
        INNER JOIN CampaignStatus ON Campaign.campaign_status_id = CampaignStatus.id
        LEFT OUTER JOIN Publisher ON Affiliate.affid = Publisher.affid
    GROUP BY
         COALESCE(Publisher.name, Affiliate.name)
        ,ItemAccountingStatus.name
        ,CampaignStatus.name    
    HAVING (
        ItemAccountingStatus.name='default'
        and CampaignStatus.name='Verified'
    )
) A
WHERE A.Publisher='AdsMain LLC'