SQL - 添加计数 if 语句

Question

我是一个相对较新的 SQL 用户，所以请耐心等待:)

我目前的查询如下...

SELECT DISTINCT 
  DMA.Name, 
  CLI.CNum, 
  CLI.LNum, 
  CLI.TSC, 
  CLI.FromDate, 
  CLI.ToDate
FROM PurchasedSites INNER JOIN
  CLI ON PS.CNum = CLI.CNum INNER JOIN
  S ON PS.SID = S.SID INNER JOIN
  DMA ON S.DMA = DMA.ID INNER JOIN
  PSM ON PS.PSID = PSM.PSID INNER JOIN
  C ON CLI.CNum = C.CNum
WHERE (PSM.MT_bmID = 10) AND 
  (CLI.FromDate <= '2013-03-31') AND 
  (CLI.ToDate >= '2013-03-01') AND 
  (S.DMA IN ('134', '113', '38', '147', '169', '24', '50', '198', '9', '55')) AND 
  (C.CS LIKE 'Active') AND 
  (PS.CNum Like 'C%') AND 
  (CLI.TSC >= 1)

如果 PS.DC = 1，我希望它计算 S.SID 的数量(或者它可以是 PS.SID - 这是相同的数据)。如果有 PS.DC = 1，那么它可以返回一个数字 0 .

我不知道把它放在哪里或把它写好。目前的结果看起来像...

Name              CNum     LNum   TSC   FromDate     ToDate
Detroit, MI       C147157   22     2    2013-03-18   2013-06-10 
Atlanta, GA       C146525   112    2    2013-01-28   2013-03-03 
Washington, DC    C146538   20     1    2013-02-06   2013-03-05
Los Angeles, CA   C146119   45     3    2013-01-01   2013-11-30 

想法？

Answer 1

您的意思是计算 PS.DC = 1 的条目，而不是计算 PS.DC 是其他任何条目的条目吗？

SELECT DMA.Name, 
CLI.CNum, 
CLI.LNum, 
CLI.TSC, 
CLI.FromDate, 
CLI.ToDate,
SUM(CASE WHEN PS.DC = 1 THEN 1.0 ELSE 0.0 END) AS CountDC
FROM PurchasedSites PS
INNER JOIN CLI ON PS.CNum = CLI.CNum 
INNER JOIN S ON PS.SID = S.SID 
INNER JOIN DMA ON S.DMA = DMA.ID 
INNER JOIN PSM ON PS.PSID = PSM.PSID 
INNER JOIN C ON CLI.CNum = C.CNum
WHERE PSM.MT_bmID = 10
AND CLI.FromDate <= '2013-03-31'
AND CLI.ToDate >= '2013-03-01'
AND S.DMA IN ('134', '113', '38', '147', '169', '24', '50', '198', '9', '55') 
AND C.CS LIKE 'Active'
AND PS.CNum Like 'C%'
AND CLI.TSC >= 1
GROUP BY DMA.Name, 
CLI.CNum, 
CLI.LNum, 
CLI.TSC, 
CLI.FromDate, 
CLI.ToDate

更新:使用 SUM(CASE...) 因为 PS.DC 是位类型，无法求和。