acess分类汇总

[wszhoho]

ClassInfo表
ID classname
1 a
2 b

NewsInfo表
ID ClassID Title
1 1 taa
2 1 taasd
3 2 tbbbb

如何得出
a 2
b 1
这样的结果
也就是统计出分类表中，每个分类有多少条新闻？？

[feixianxxx]

select classname,count(*) as次数
from ClassInfo c ,NewsInfo N 
where c.id=n.ClassID 
group by classname

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('classinfo') IS NOT NULL
  DROP TABLE classinfo
GO
CREATE TABLE classinfo(ID int,classname char(1))
go
insert into classinfo
select 1,'a' union all
select 2,'b'
go
IF OBJECT_ID('NewsInfo') IS NOT NULL
  DROP TABLE NewsInfo
GO
CREATE TABLE NewsInfo(ID int,classid int,title varchar(40))
go
insert into NewsInfo
select 1,1,'taa' union all
select 2,1,'taasd' union all
select 3,2,'yadsad'
go
select classname,count(*) as次数
from ClassInfo c ,NewsInfo N 
where c.id=n.ClassID 
group by classname


/*------------
classname as次数
--------- -----------
a         2
b         1

-------*/