现有2个表
table1
content code
1223 1
23444 2
table2
id sender code
1 3dfer 1
2 344 1
3 ff 2
4 de 1
5 dfer 2
我希望做一个select语句，得到的是第一个表所有的行，并且希望多一列，该列显示具有该行code值在table2中的行数。即希望得到的结果是：
内容 编码 个数
1223 1 3
23444 2 2
请教这个select语句应该怎么写啊？
------解决方案--------------------
select t1.*, t2.cnt
from table1 t1 left join
(select a.code, cnt = count(1)
from table1 a, table2 b
where a.code = b.code
group by a.code) t2
on t1.code = t2.code
------解决方案--------------------
declare @a table(
content int,
code int)
insert @a select 1223, 1
union all select 23444, 2
declare @b table(
id int,
sender varchar(10),
code int)
insert @b select 1, '3dfer ', 1
union all select 2, '344 ', 1
union all select 3, 'ff ', 2
union all select 4, 'de ', 1
union all select 5, 'dfer ', 2
select t1.*, t2.cnt
from @a t1 left join
(select a.code, cnt = count(1)
from @a a, @b b
where a.code = b.code
group by a.code) t2
on t1.code = t2.code
/*
content code cnt
----------- ----------- -----------
1223 1 3
23444 2 2
（所影响的行数为 2 行）
*/
------解决方案--------------------
create table t(
content int,
code int)
insert t select 1223, 1
union all select 23444, 2
create table t1(
id int,
sender varchar(10),
code int)
insert t1 select 1, '3dfer ', 1
union all select 2, '344 ', 1
union all select 3, 'ff ', 2
union all select 4, 'de ', 1
union all select 5, 'dfer ', 2
select a.*,quantity=(select count(*)from t1 where a.code=code)
from t a
content code quantity
----------- ----------- -----------
1223 1 3
23444 2 2
(2 row(s) affected)
------解决方案--------------------
select content,code,count(1)
from table2 a
left join from table1 b on a.code=b.code
group by content,code
------解决方案--------------------
a 和 b 是指一個表的別名,就好比我們人也有多個名字一樣
------解决方案--------------------