• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求一条sql语句,大家帮忙

landog 2003-05-08 02:31:33
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
...全文
14 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
fyg_02971 2003-05-08
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
回复
ybz3721 2003-05-08
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 行)


已测试。。。


回复
CrazyFor 2003-05-08
select *, (select count(*) from resources where TID = A.TID) as count from res_types as A
回复
yoki 2003-05-08

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
回复
firetoucher 2003-05-08
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
回复
dapper 2003-05-08
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

忘了分组了。呵呵。
回复
dapper 2003-05-08
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
回复
happydreamer 2003-05-08

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
回复
select *, (select count(*) from resources where TID = A.TID) from res_types as A
回复
landog 2003-05-08
对亚,俺想知道一条语句可以实现不?
回复
ralpher2017 2003-05-08
用游标和临时表可以实现,不过我想应该还有其他的方法能得到,gz中....
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-08 02:31
社区公告
暂无公告