时间戳

首页 > 解决方案 > 在同一列上使用两个 Count() 的问题

tsql - 在同一列上使用两个 Count() 的问题

问题描述

我试图在同一列上获得两个不同的计数。这两个计数在单独的查询中工作正常，但我只是不确定如何将它们放在一个查询中。我有两张桌子，分别是DailyFieldRecord和AB953。DailyFieldRecord包含：DailyFieldRecordID和ActivityCodeID。该AB953表包含：DailyFieldRecordID, ItemID, and GroupID。Count1将返回DailyfieldrecordID's包含ActivityCodeID=387andGroupID=260和 DON'T have的计数ItemID in (1302,1303,1305,1306)。Count2将返回DailyfieldrecordID's包含ActivityCodeID=387andGroupID=260和 HAVE的计数ItemID in (1302,1303,1305,1306)。所以它们是相似的，但我只是不确定如何将它们组合成一个查询。我在两个约束之间的 where 子句中放置了一个 OR，我知道这是错误的，但我只是不确定如何执行此操作。

    DailyFieldRecord:                   AB953:
DailyFieldRecordID ActivityCodeID    DailyFieldRecordID:  ItemID: GroupID:
   657                 387                657               1305    210
   888                 420                657               1333    260
   672                 387                657               1335    260  
                                          888               1302    210
                                          888               1336    260
                                          672               1327    260
                                          672               1334    260 
                                          672               1335    260
Expected Output:
Count1:      Count2:
   3            2
Count1 is supposed to count:                Count2 is supposed to count:
   672    1327    260                  657     1333     260
   672    1334    260                  657     1335     260
   672    1335    260

SELECT COUNT(ActivityCodeID) as Count1, COUNT(ActivityCodeID) As Count2
    FROM AB953 ab
    JOIN DailyFieldRecordID dfr on 
         dfr.DailyFieldRecordID=ab.DailyFieldRecordID

     WHERE dfr.ActivityCodeID=387 and ab.GroupID=260 and NOT exists(
          select b.DailyFieldRecordID
          FROM DailyFieldRecordID b
          WHERE ab.DailyFieldRecordID = b.DailyFeildRecordID AND 
                b.ItemID in (1302,1303,1305,1306))
     OR
          dfr.ActivityCodeID=387 and ab.GroupID=260 and exists(
          select b.DailyFieldRecordID
          FROM DailyFieldRecordID b
          WHERE ab.DailyFieldRecordID = b.DailyFeildRecordID AND 
                b.ItemID in (1302,1303,1305,1306))

标签： tsqlcount

解决方案

首先找到DailyFieldRecordIDfor ItemID in (1302,1303,1305,1306)(Query 1) 的列表，然后将查询合并到您的最终查询中。

SQL小提琴

查询一：

SELECT AB1.DailyFieldRecordID,SUM(CASE WHEN AB1.ItemID IN (1302,1303,1305,1306) THEN 1 ELSE 0 END)
FROM AB953 AB1
GROUP BY AB1.DailyFieldRecordID

最终查询：

SELECT SUM(CASE WHEN EX=0 THEN 1 ELSE 0 END) AS COUNT1,SUM(EX) AS COUNT2
FROM AB953 ab
INNER JOIN DailyFieldRecord dfr
    ON dfr.DailyFieldRecordID = ab.DailyFieldRecordID
LEFT JOIN ( SELECT AB1.DailyFieldRecordID,SUM(CASE WHEN AB1.ItemID IN (1302,1303,1305,1306) THEN 1 ELSE 0 END) AS EX
            FROM AB953 AB1
            GROUP BY AB1.DailyFieldRecordID) T
    ON dfr.DailyFieldRecordID = T.DailyFieldRecordID
WHERE dfr.ActivityCodeID = 387
    AND ab.GroupID = 260

推荐阅读