id uid title
1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 3 ee
6 2 ff
7 3 gg
表B id name
1 张三
2 李四
3 黄五 用一句SQL需要按表B 得到表A对应的数量和表B的id,name。比如需要得到 1 张三(3)
2 李四(2)
3 黄五(2)
1 1 aa
2 1 bb
3 1 cc
4 2 dd
5 3 ee
6 2 ff
7 3 gg
表B id name
1 张三
2 李四
3 黄五 用一句SQL需要按表B 得到表A对应的数量和表B的id,name。比如需要得到 1 张三(3)
2 李四(2)
3 黄五(2)
where A.id = B.uid
where A.id = B.uid
from tbb a
join tba b
on a.id=b.uid
group by a.id,a.name
select A.id,A.name+'('+isnull(ltrim(B.num),0)+')' from 表B a
left join (select uid,count(title) num from 表A) B
on A.id = B.uid
declare @tb table ([id] int,[uid] int,[title] nvarchar(2))
Insert into @tb
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,1,'cc' union all
select 4,2,'dd' union all
select 5,3,'ee' union all
select 6,2,'ff' union all
select 7,3,'gg'
--Select * from @tbdeclare @tbB table ([id] int,[name] nvarchar(4))
Insert into @tbB
select 1,'张三' union all
select 2,'李四' union all
select 3,'黄五'
--Select * from @tbBselect a.[uid] ,b.[name] + '( '+ convert(nvarchar(10),count(1)) + ')' as 'usercount' from @tb a
left join @tbB b on b.[id] = a.[uid]
group by a.[uid],b.[name]
order by uid
/*
uid usercount
----------- -----------------
1 张三( 3)
2 李四( 2)
3 黄五( 2)(3 row(s) affected)
*/
create table ta(id int identity(1,1),uid int,title varchar(20))
insert into ta select 1,'aa'
insert into ta select 1,'bb'
insert into ta select 1,'cc'
insert into ta select 2,'dd'
insert into ta select 3,'ee'
insert into ta select 2,'ff'
insert into ta select 3,'gg'
go
create table tb(id int identity(1,1),name varchar(10))
insert into tb select '张三'
insert into tb select '李四'
insert into tb select '黄五'select A.id,A.name+'('+cast(B.num as varchar(10))+')' name from tb A,(select uid,count(title) num from ta group by uid) B
where A.id = B.uiddrop table ta,tb
declare @ta table(id int,uid int,title varchar(20))
insert into @ta select 1,1,'aa'
insert into @ta select 2,1,'bb'
insert into @ta select 3,1,'cc'
insert into @ta select 4,2,'dd'
insert into @ta select 5,3,'ee'
insert into @ta select 6,2,'ff'
insert into @ta select 7,3,'gg'declare @tb table (id int,name varchar(20))
insert into @tb select 1,'张三'
insert into @tb select 2,'李四'
insert into @tb select 3,'黄五'select b.id,b.name+'('+ltrim(count(1))+')' as name
from @ta a join @tb b on a.uid=b.id
group by b.id,b.nameid name
1 张三(3)
2 李四(2)
3 黄五(2)