select a.name,b.cnt from 书类型表 a join (select pid,count(*) cnt from 书列表 group by pid) b on a.id=b.id
select name,[count]=(select count(1) from 表B where pid=a.id) from 表A a
--> 测试数据: #tba if object_id('tempdb.dbo.#tba') is not null drop table #tba create table #tba (id int,name varchar(4)) insert into #tba select 1,'小说' union all select 2,'课本' --> 测试数据: #tbb if object_id('tempdb.dbo.#tbb') is not null drop table #tbb create table #tbb (id int,name varchar(8),pid int) insert into #tbb select 1,'家',1 union all select 2,'春',1 union all select 3,'秋',1 union all select 4,'语文课本',2 union all select 5,'数学课本',2 goselect a.name,ltrim(b.cnt)+'本' as [count] from #tba a join (select pid ,count(*) cnt from #tbb group by pid) b on a.id=b.pid go drop table #tba,#tbb/* name count ---- -------------- 小说 3本 课本 2本(2 row(s) affected) */
--> 测试数据: @a declare @a table (id int,name varchar(4)) insert into @a select 1,'小说' union all select 2,'课本' --> 测试数据: @B declare @B table (id int,name varchar(8),pid int) insert into @B select 1,'家',1 union all select 2,'春',1 union all select 3,'秋',1 union all select 4,'语文课本',2 union all select 5,'数学课本',2 select a.name,[count]=count(b.id) from @a a left join @b b on a.id=b.pid group by a.name
select a.name,count(*) from a,b where a.id=b.pid group by a.name
select a.name,count(b.pid) from a,b where a.pid=a.id group by name,pid
declare @t table([id] int,[name] varchar(10)) insert @t select 1,'小说' union all select 2,'课本'declare @b table([id] int,[name] varchar(10),pid int) insert @b select 1,'家',1 union all select 2,'春',1 union all select 3,'秋',1 union all select 4,'语文课本',2 union all select 5,'数学课本',2select a.[name],'count'=cast(b.count as varchar)+'本' from @t a join (select pid,'count'=count(*) from @b group by pid)b on a.[id]=b.pid /* name count ---------- -------------------------------- 小说 3本 课本 2本(所影响的行数为 2 行)*/
select B.count(pid),A.name from A left join on B.pid=A.id group by name
declare @a table(id int,name varchar(20)) declare @b table(id int,name varchar(20),pid int) insert into @a select 1,'小说' union all select 2,'课本' insert into @b select 1,'家',1 union all select 2,'春',1union all select 3,'秋',1union all select 4,'语文课本',2 union all select 5,'数学课本',2select a.name,count(b.pid) from @a a,@b b where a.id=b.pid group by a.name,b.pid(2 row(s) affected) (5 row(s) affected)name -------------------- ----------- 小? 3 ?本 2(2 row(s) affected)
select a.name,b.cnt from 书类型表 a join (select pid,count(id) cnt from 书列表 group by pid) b on a.id=b.pid
select name,(select count(1) from b where a.id=pid) as [count] from a
from 书类型表 a
join (select pid,count(*) cnt from 书列表 group by pid) b
on a.id=b.id
select name,[count]=(select count(1) from 表B where pid=a.id)
from 表A a
if object_id('tempdb.dbo.#tba') is not null drop table #tba
create table #tba (id int,name varchar(4))
insert into #tba
select 1,'小说' union all
select 2,'课本'
--> 测试数据: #tbb
if object_id('tempdb.dbo.#tbb') is not null drop table #tbb
create table #tbb (id int,name varchar(8),pid int)
insert into #tbb
select 1,'家',1 union all
select 2,'春',1 union all
select 3,'秋',1 union all
select 4,'语文课本',2 union all
select 5,'数学课本',2
goselect a.name,ltrim(b.cnt)+'本' as [count]
from #tba a
join (select pid ,count(*) cnt from #tbb group by pid) b
on a.id=b.pid
go
drop table #tba,#tbb/*
name count
---- --------------
小说 3本
课本 2本(2 row(s) affected)
*/
--> 测试数据: @a
declare @a table (id int,name varchar(4))
insert into @a
select 1,'小说' union all
select 2,'课本'
--> 测试数据: @B
declare @B table (id int,name varchar(8),pid int)
insert into @B
select 1,'家',1 union all
select 2,'春',1 union all
select 3,'秋',1 union all
select 4,'语文课本',2 union all
select 5,'数学课本',2
select a.name,[count]=count(b.id)
from @a a left join @b b
on a.id=b.pid group by a.name
insert @t select 1,'小说'
union all select 2,'课本'declare @b table([id] int,[name] varchar(10),pid int)
insert @b select 1,'家',1
union all select 2,'春',1
union all select 3,'秋',1
union all select 4,'语文课本',2
union all select 5,'数学课本',2select a.[name],'count'=cast(b.count as varchar)+'本'
from @t a join
(select pid,'count'=count(*) from @b group by pid)b
on a.[id]=b.pid
/*
name count
---------- --------------------------------
小说 3本
课本 2本(所影响的行数为 2 行)*/
B.pid=A.id group by name
declare @b table(id int,name varchar(20),pid int)
insert into @a select 1,'小说' union all
select 2,'课本'
insert into @b select 1,'家',1 union all
select 2,'春',1union all
select 3,'秋',1union all
select 4,'语文课本',2 union all
select 5,'数学课本',2select a.name,count(b.pid) from @a a,@b b where a.id=b.pid group by a.name,b.pid(2 row(s) affected)
(5 row(s) affected)name
-------------------- -----------
小? 3
?本 2(2 row(s) affected)
from 书类型表 a
join (select pid,count(id) cnt from 书列表 group by pid) b
on a.id=b.pid