SQL Server的分组依据

小编典典

SQL Server的分组依据

sql

我有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

阅读 294

2021-05-30

共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

2021-05-30