表1
a,b,c,d,e,f,g,h
b,b,c,d,e,f,g,h
c,b,c,d,e,f,g,h 表2
a,1
a,2
a,3
b,b
b,c
c,1
c,3
c,4 得到如下的结果,将表1的数据列出,同时显示表2中等于表1中第一列的数据有多少条
a,b,c,d,e,f,g,h,3
b,b,c,d,e,f,g,h,2
c,b,c,d,e,f,g,h,3
a,b,c,d,e,f,g,h
b,b,c,d,e,f,g,h
c,b,c,d,e,f,g,h 表2
a,1
a,2
a,3
b,b
b,c
c,1
c,3
c,4 得到如下的结果,将表1的数据列出,同时显示表2中等于表1中第一列的数据有多少条
a,b,c,d,e,f,g,h,3
b,b,c,d,e,f,g,h,2
c,b,c,d,e,f,g,h,3
on a.id==c.id
declare @1 table (C1 char(1),C2 char(1),C3 char(1),C4 char(1),C5 char(1),C6 char(1),C7 char(1),C8 char(1))
insert into @1
select 'a','b','c','d','e','f','g','h' union all
select 'b','b','c','d','e','f','g','h' union all
select 'c','b','c','d','e','f','g','h'
--> 测试数据: @2
declare @2 table (C1 char(1),C2 char(1))
insert into @2
select 'a','1' union all
select 'a','2' union all
select 'a','3' union all
select 'b','b' union all
select 'b','c' union all
select 'c','1' union all
select 'c','3' union all
select 'c','4'select a.*,C9=(select count(1) from @2 where C1=a.C1) from @1 a/*
a b c d e f g h 3
b b c d e f g h 2
c b c d e f g h 3
*/
select 表1.*,(select count(*) from 表2 where 表1.a=表2.a1) as nums from 表1
if exists(select * from sysobjects where name='t1')
drop table t1
if exists(select * from sysobjects where name='t2')
drop table t2
create table t1(c1 varchar(2),c2 varchar(2),c3 varchar(2))
create table t2(c1 varchar(2),c2 varchar(2))
insert into t1(c1,c2,c3)
select 'a','b','d'
union select 'b','c','d'
union select 'c','d','e'
insert into t2(c1)
select 'a'
union all select 'a'
union all select 'a'
union all select 'a'
union all select 'b'
union all select 'b'
union all select 'c'select t1.* ,(select count(t2.c1) from t2 where t2.c1=t1.c1)as c from t1
drop table t1,t2
select a.*,c.cnt from a inner join (select id,count(*) cnt from b group by id) c
on a.id==c.id