首页 > sql > SQL将count()特定记录放在另一个* AND *相同的表中

SQL将count()特定记录放在另一个* AND *相同的表中 (SQL to count() specific records in in a different *AND* same table)

问题

这与我最近提出的另一个问题类似,但更进一步。

我之前的问题询问如何构建一个视图,tblCurrent同时检索来自的特定相关记录的计数tblHistorical

现在想知道如何做同样的事情,但是添加两个额外的列,根据同一个tblCurrent表中的特定条件计算记录。

我有两张桌子:

tblCurrent 
 Ref  | CustomerID | Category | Subcategory | Resolved | ... .. .. .  . |
X001  | 001        | Sales  |  Major      |  TRUE  
X002  | 002        | Sales  | Minor      |  FALSE
X003  | 001        | Marketing | Corp      |  TRUE  


tblHistorical 
 Ref   | ... .. .. .  . |  Missing | Matched
X001   | ... .. .. .  . |  TRUE    | FALSE
X001   | ... .. .. .  . |  FALSE   | FALSE
X002   | ... .. .. .  . |  TRUE    | TRUE
X002   | ... .. .. .  . |  TRUE    | FALSE
X003   | ... .. .. .  . |  FALSE   | FALSE
X003   | ... .. .. .  . |  TRUE    | TRUE

Ref在tblCurrent中是唯一的,但在历史中不是。

我被告知并成功地完成了上一个问题的以下工作:

SELECT  a.ref,
    SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue,
    SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse,
    SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue
FROM    tblCurrent a
    LEFT JOIN tblHistorical b
        on a.ref = b.ref
GROUP BY a.ref

问:如何添加两个额外的列(对于上述解决方案)计数:

  • tblCurrent匹配相同的记录数CustomerID+ Category(但不是当前记录)AND Resolved = False。
  • 在记录数tblCurrent那场比赛相同CustomerID+ Category+ SubCategory(但不是当前记录),并解决=假。

解决方法

试试这个:

SELECT a.ref,SUM(例如b.Missing ='True'THEN 1 ELSE 0 END)missingTrue,SUM(CASE WHEN.Missing ='False'THEN 1 ELSE 0 END)missingFalse,SUM(CASE WHH.Matched ='True'THEN 1 ELSE 0 END)matchTrue,COUNT(DISTINCT a.CustomerID)as CustomerID_count -

编辑:

根据您更新的问题,如果我正确理解您的问题,

;WITH 
CATEGORY   AS 
        (SELECT CustomerID ,Category,COUNT(*) AS CNT
        FROM tblCurrent
        GROUP BY CustomerID ,Category),

SUBCATEGORY AS 
        (SELECT CustomerID ,Category,SubCategory,COUNT(*) AS CNT
        FROM tblCurrent
        GROUP BY CustomerID ,Category,SubCategory),

MAIN        AS (
SELECT  a.ref,
        SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue,
        SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse,
        SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue
        FROM    tblCurrent a
        LEFT JOIN tblHistorical b
            on a.ref = b.ref
        GROUP BY a.ref
)
SELECT M.* ,C.CNT AS CATEGORY_COUNT,
       S.CNT AS SUBCATEGORY_COUNT
FROM    MAIN M
JOIN    tblCurrent T
ON      M.ref=T.ref
JOIN    CATEGORY C
ON      C.CustomerID=T.CustomerID
AND     C.Category=T.Category
JOIN    SUBCATEGORY S
ON      S.CustomerID=T.CustomerID
AND     C.Category=T.Category
AND     S.SubCategory=T.SubCategory

问题

This is similar to another question I asked recently, but goes a little further.

My previous question asked how I could construct a view with tblCurrent whilst retrieving a count of specific, related records from tblHistorical.

I now want to know how to do the same, but to add two additional columns that count records based on specific criteria from the same tblCurrent table.

I have two tables:

tblCurrent 
 Ref  | CustomerID | Category | Subcategory | Resolved | ... .. .. .  . |
X001  | 001        | Sales  |  Major      |  TRUE  
X002  | 002        | Sales  | Minor      |  FALSE
X003  | 001        | Marketing | Corp      |  TRUE  


tblHistorical 
 Ref   | ... .. .. .  . |  Missing | Matched
X001   | ... .. .. .  . |  TRUE    | FALSE
X001   | ... .. .. .  . |  FALSE   | FALSE
X002   | ... .. .. .  . |  TRUE    | TRUE
X002   | ... .. .. .  . |  TRUE    | FALSE
X003   | ... .. .. .  . |  FALSE   | FALSE
X003   | ... .. .. .  . |  TRUE    | TRUE

Ref is unique in tblCurrent, but not in Historical.

I was advised and successfully got to work the following for the previous question:

SELECT  a.ref,
    SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue,
    SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse,
    SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue
FROM    tblCurrent a
    LEFT JOIN tblHistorical b
        on a.ref = b.ref
GROUP BY a.ref

Q: How do I add two additional columns (to the above solution) that count:

  • Number of records in tblCurrent that match the same CustomerID + Category (but not current record) AND Resolved=False.
  • Number of records in tblCurrent that match the same CustomerID + Category + SubCategory (but not current record) AND Resolved=False.

解决方法

try this:

SELECT a.ref, SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue, SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse, SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue , COUNT(DISTINCT a.CustomerID) as CustomerID_count --< add this line FROM tblCurrent a LEFT JOIN tblHistorical b on a.ref = b.ref GROUP BY a.ref

EDIT:

As per your updated question, If I understand your question correctly,

;WITH 
CATEGORY   AS 
        (SELECT CustomerID ,Category,COUNT(*) AS CNT
        FROM tblCurrent
        GROUP BY CustomerID ,Category),

SUBCATEGORY AS 
        (SELECT CustomerID ,Category,SubCategory,COUNT(*) AS CNT
        FROM tblCurrent
        GROUP BY CustomerID ,Category,SubCategory),

MAIN        AS (
SELECT  a.ref,
        SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue,
        SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse,
        SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue
        FROM    tblCurrent a
        LEFT JOIN tblHistorical b
            on a.ref = b.ref
        GROUP BY a.ref
)
SELECT M.* ,C.CNT AS CATEGORY_COUNT,
       S.CNT AS SUBCATEGORY_COUNT
FROM    MAIN M
JOIN    tblCurrent T
ON      M.ref=T.ref
JOIN    CATEGORY C
ON      C.CustomerID=T.CustomerID
AND     C.Category=T.Category
JOIN    SUBCATEGORY S
ON      S.CustomerID=T.CustomerID
AND     C.Category=T.Category
AND     S.SubCategory=T.SubCategory
相似信息