sql server语言 - 将sql SELECT COUNT结果作为文本添加到现有列中

Question

我有两个表:

1. TABLE [dbo].[ItemCategories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CategoryId] [int] NULL,
    [StockId] [int] NULL,
2. TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentCategoryId] [int] NULL,
    [CategoryName] [nvarchar](100) NULL,
    [Slug] [nvarchar](150) NULL

SQL Server 2012

SELECT [CategoryName], [Slug], [ParentCategoryId], [Id] 
FROM [Categories] 
ORDER BY [ParentCategoryId] DESC

返回这些行

[CategoryName]  [Slug]  [ParentCategoryId]  [Id]
Exercise        exercise    42               46
Fashion         fashion     42               47

我有第二个查询:

SELECT COUNT(*)
FROM [ItemCategories]
WHERE CategoryId = '46' <--- This Id is the same as [Id] from the first query

如何修改第一个查询以将第二个查询的总数添加到返回的CategoryName列(作为单个字符串)?

:

 [CategoryName]     [Slug]   [ParentCategoryId] [Id]
Exercise (31)       exercise    42               46
Fashion (56)        fashion     42               47

我已经创建了这个连接，但我不知道如何添加COUNT(*)作为文本

SELECT [CategoryName], [Slug], [ParentCategoryId], [Categories].[Id] 
FROM [Categories] 
INNER JOIN [ItemCategories] ON [Categories].[Id]=[ItemCategories].[CategoryId] 
ORDER BY [ParentCategoryId] DESC

Answer 1

可以使用count(*)窗口函数。我会把它放在一个单独的列中，但你可以这样做:

SELECT [CategoryName] + ' (' + cast(count(*) over (partition by Id) as varchar(255)) + ')',
       [Slug], [ParentCategoryId], [Id] 
FROM [Categories] 
ORDER BY [ParentCategoryId] DESC;

编辑:

对于两个表，使用JOIN和GROUP BY:

SELECT c.CategoryName + ' (' + cast(count(ic.Id) as varchar(255)) + ')',
      c.Slug, c.ParentCategoryId, c.Id
FROM Categories c LEFT JOIN
     ItemCategories ic
     on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
ORDER BY ParentCategoryId DESC;