SQL Server 计数分组依据

Question

我有3张桌子。我想按 item.name 进行分组并计算它在 idetail 中找到的次数。此查询仅计算所有行，而不计算按行分组的行。

有人知道如何修复它吗？

SELECT i.name, COUNT(i.name)
FROM item AS i
INNER JOIN item_category AS ic ON i.i_category_id = ic.ic_id
INNER JOIN idetail AS id ON ic.ic_id = id.id_category_id
WHERE ic.ic_id = 1002
GROUP BY i.name

Answer 1

这就是你想要的：

select x.name
     , count(*) as cntNames
     , sum(x.cntDetails) as cntDetails
  from (
        SELECT i.name, COUNT(*) as cntDetails
          FROM item AS i
         INNER JOIN item_category AS ic ON i.i_category_id = ic.ic_id
         INNER JOIN idetail AS id ON ic.ic_id = id.id_category_id
         WHERE ic.ic_id = 1002
          -- NOTICE THE 2nd Value in the group by!
         GROUP BY i.name,id.id_category_id
       ) x
  group by name

Answer 2

Select  Count(*)
From    (
            Select i.name, Count(i.name)
            From item As I
                Join item_category As ic
                    On ic.ic_id = i.category_id
                Join idetail as ID
                    On ID.id_category_id = ic.ic_id
            Where ic.ic_id = 1002
            Group By i.name
            ) As Z

如果您需要所有三列并且您使用的是 SQL Server 2005+

With Items As
    (
    Select I.name, Count(i.name) As ItemCount
    From item As I
        Join item_category As ic
            On ic.ic_id = i.category_id
        Join idetail as ID
            On ID.id_category_id = ic.ic_id
    Where ic.ic_id = 1002
    Group By i.name
    )
Select Name, ItemCount
    ,   (
        Select Count(*)
        From Items
        ) As OverallCount
From Items  

如果您使用的是 SQL Server 2005+，则另一个更简单的版本。

Select i.name
    , Count(i.name) As CountByName
    , Count(*) Over() As TotalCount
From item As I
    Join item_category As ic
        On ic.ic_id = i.category_id
    Join idetail as ID
        On ID.id_category_id = ic.ic_id
Where ic.ic_id = 1002
Group By i.name