select *, (select count(*) from resources where TID = A.TID) from res_types as A
解决方案 »
- T-SQL 批量生成指定范围和长度的随机无重复字符串
- 两表查询。一个表数据多 。一个表数据少,得到全部数据该怎么写查询语句
- 求助,关于进销存的采购数量问题,求思路
- 如何实现表数据同步?
- 恢复xp_cmdshell问题
- 怎样将存储过程返回的记录集保存为表??请高手指教,UP有分
- 数据合并问题?
- 关于数据库转移的问题,请大侠指点!
- 请教这个sql语句怎么写?
- SQL SERVER创建新表(或VIEW)时出现an unexpected error happend (SQL SERVER7+WIN2000)
- C#+MS-SQL问题
- 如果解决给300分,ACCESS数据库求一个SQL语句.欢迎所有人来UP
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
from
res_types a,
(select TID, count(*) c from resources group by TID) b
where a.TID = b.TID
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
from res_types a,resources b
where a.tid=b.tid
group by a.tid,a.name,a.memo(所影响的行数为 2 行)
已测试
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