求一条sql语句，大家帮忙

[landog]

res_types表

TID Name Memo
1 n1 m1
2 n2 m2

resources 表

RID TID
1 1
2 1
3 2

如何查到所有res_types中的数据，同时查到该条数据TID相同的resources中记录个数
比如得到
TID Name Memo count()
1 n1 m1 2
2 n2 m2 1


3ks

[ralpher2017]

用游标和临时表可以实现，不过我想应该还有其他的方法能得到，gz中....

[landog]

对亚，俺想知道一条语句可以实现不？

[愉快的登山者]

select *, (select count(*) from resources where TID = A.TID) from res_types as A

[happydreamer]

select a.*,b.count1 as count()
from res_types a
join
(select tid,count(rid) as count1 from resources group by tid)b
on a.tid=b.tid

[dapper]

select a.tid,a.name,a.memo,b.tid_count from res_types a left join (select TID ,count(TID) as TID_Count from res_types ) b on a.tid=b.tid

[dapper]

select a.tid,a.name,a.memo,b.tid_count from res_types a left join (select TID ,count(TID) as TID_Count from res_types group by tid) b on a.tid=b.tid

忘了分组了。呵呵。

[firetoucher]

select a.TID, Name, Memo, c
from
res_types a,
(select TID, count(*) c from resources group by TID) b
where a.TID = b.TID

[yoki]

create table res_types(TID int, Name varchar(20), Memo varchar(200))
insert into res_types values(1, 'n1', 'm1')
insert into res_types values(2, 'n2', 'm2')

create table resources(RID int,TID int)
insert into resources values( 1 , 1)
insert into resources values( 2 , 1)
insert into resources values( 3 , 2)

select a.*,(select count(tid) from resources where tid=a.tid) as 数目 from res_types a

结果：
-----------------------------*/
TID Name Memo 数目
--- ----- -----
1 n1 m1 2
2 n2 m2 1

[CrazyFor]

select *, (select count(*) from resources where TID = A.TID) as count from res_types as A

[ybz3721]

select a.tid tid,a.name name,a.memo memo,count(b.rid) count
from res_types a,resources b
where a.tid=b.tid
group by a.tid,a.name,a.memo

（所影响的行数为 2 行）


已测试。。。

[fyg_02971]

create table res_type (tid int,name varchar(20),memo varchar(20))
create table resources (rid int ,tid int)

insert res_type values(1,'n1','m1')
insert res_type values(2,'n2','m2')

insert resources values(1,1)
insert resources values(2,1)
insert resources values(3,2)

select res.*,(select count(tid) from resources where tid = res.tid) from res_type res