SQL Server：将项目分组不起作用

I am new to SQL and need to create a stored procedure that fetches all items from a table + groups them by category and adds the count for each category.

我是SQL的新手,需要创建一个存储过程,从表中取出所有项目+按类别对它们进行分组,并为每个类别添加计数。

So far I have the following which all the items correctly but does not group and count them anymore by category (see below). The issue here is that it lists all items separately instead of grouping them under the corresponding group. I assume I have to nest the select here but don't know how to do that.

到目前为止,我有以下所有项目正确,但不按类别分组和计算它们(见下文)。这里的问题是它分别列出所有项目,而不是将它们分组到相应的组下。我假设我必须在这里嵌套选择,但不知道该怎么做。

Can someone here help me with this ?

有人可以帮我这个吗?

My stored procedure:

我的存储过程:

  CREATE PROCEDURE [dbo].[FetchRequests]

  AS
  BEGIN
  SET NOCOUNT ON;
  SELECT              categoryX, itemID
                      COUNT(*) AS groupCount,
                      MAX(dateX) AS groupNewest
  FROM                LogRequests
  WHERE               logStatus = 'active'
  GROUP BY            categoryX, itemID
  ORDER BY            groupCount desc, categoryX
  FOR XML PATH('categoryX'), ELEMENTS, TYPE, ROOT('ranks')
  END

Current result:

<ranks>
  <categoryX>
    <categoryX>Category 1</categoryX>
    <itemID>ID 1</itemID>
    <groupCount>1</groupCount>
    <groupNewest>2013-11-21</groupNewest>
  </categoryX>
  <categoryX>
    <categoryX>Category 2</categoryX>
    <itemID>ID 2</itemID>
    <groupCount>1</groupCount>
    <groupNewest>2013-10-30</groupNewest>
  </categoryX>
  // ...
</ranks>

Expected result:

<ranks>
  <categoryX>
    <categoryX>Category 1</categoryX>
    <groupCount>3</groupCount>
    <groupNewest>2013-11-21</groupNewest>
      <itemID>ID 1</itemID>
      <itemID>ID 2</itemID>
      <itemID>ID 3</itemID>
    <categoryX>Category 2</categoryX>
    <groupCount>4</groupCount>
    <groupNewest>2013-10-15</groupNewest>
      <itemID>ID 1</itemID>
      <itemID>ID 2</itemID>
      <itemID>ID 3</itemID>
      <itemID>ID 4</itemID>
    // ...
  </categoryX>
</ranks>

Many thanks for any help with this, Tim.

蒂姆,非常感谢你提供的任何帮助。

1 个解决方案

#1

select
    lr.categoryX,
    count(*) as groupCount,
    max(lr.dateX) as groupNewest,
    (
        select t.ItemID
        from LogRequests as t
        where t.logStatus = 'active' and t.categoryX = lr.categoryX
        for xml path(''), type
    )
from LogRequests as lr
where lr.logStatus = 'active'
group by lr.categoryX
for xml path('categoryX'), root('ranks')