在线等 关联表查询的 求count简单

[Transitional]

分类表
ID UserName Name
1 aaa 学习

课程表
ID Name 分类ID
1 语文 1
2 数学 1

要求查出结果：
----------------------------------------------
分类ID 分类名称
1 学习(2)

[xiaodongni]

with cte as 
(select 1 as id,'aaa' as username,'学习' as name),
cte1 as 
(select 1 as id,'语文'as name ,1 as fid union all
 select 2 as id,'数学' ,1  )
 select a.id as 分类ID,a.name+'('+convert(varchar(3),COUNT(a.id))+')'as 分类名称 from cte as a  join cte1 as b
 on a.id=b.fid
 group by a.id,a.name
 
--结果
分类ID        分类名称
----------- ---------
1           学习(2)

(1 行受影响)

[还在加载中灬]

你参考一下

SELECT
    A.ID [分类ID]
    ,B.Name+'('+CAST(ISNULL(COUNT(B.ID),0)AS VARCHAR(10))+')' [分类名称]
FROM
    [分类表]A
    LEFT JOIN [课程表]B ON A.ID=B.分类ID
GROUP BY
    A.ID [分类ID]
    ,B.Name

[昵称被占用了]

select b.分类ID,a.Name + '(' + cast(count(1) as nvarchar(10)) + ')' as 分类名称
from 分类表 a,课程表 b
where a.id = b.分类ID
group by  b.分类ID,a.Name

[还在加载中灬]

SELECT
    A.ID [分类ID]
    ,A.Name+'('+CAST(ISNULL(COUNT(B.ID),0)AS VARCHAR(10))+')' [分类名称]
FROM
    [分类表]A
    LEFT JOIN [课程表]B ON A.ID=B.分类ID
GROUP BY
    A.ID [分类ID]
    ,A.Name

我上面错了，修改如下

[还在加载中灬]

SELECT
    A.ID [分类ID]
    ,A.Name+'('+CAST(ISNULL(COUNT(B.ID),0)AS VARCHAR(10))+')' [分类名称]
FROM
    [分类表]A
    LEFT JOIN [课程表]B ON A.ID=B.[分类ID]
GROUP BY
    A.ID
    ,A.Name

[卖水果的net]

-- 
select id,name + (select '(' + cast(COUNT(*) as varchar(10)) + ')' from 课程表 where a.id = 分类ID)
from 分类表 a 

[njz168]

分类表 ID UserName Name 1 aaa 学习 课程表 ID Name 分类ID 1 语文 1 2 数学 1

Select ID as 分类ID,Name+'('+cast(sum(b.id)  as varchar)+')' as 分类名称 from 分类表 a,课程表 b where a.id = b.分类ID group by a.ID