表一 Category 分类
categoryid name
1 中国新闻
2 美国新闻
3 国际新闻
4 军事新闻表二 News 存储新闻内容(id title foreignid,其中有[foreignid]外键到表一的[id])
想要查询出 分类名称及其对应新闻内容的数量统计
如
name total
中国新闻 [100]
美国新闻 [200]
国际新闻 [0]
军事新闻 [2]因该怎么写?SELECT Category.name, COUNT(News.newsid) AS total FROM Category
INNER JOIN News ON News.foreignid = Category.categoryid
GROUP BY Category.name这样写的话,其中(国际新闻 [0])是不显示在查询结果内的,有什么办法让count=0的也能显示在查询结果内?
categoryid name
1 中国新闻
2 美国新闻
3 国际新闻
4 军事新闻表二 News 存储新闻内容(id title foreignid,其中有[foreignid]外键到表一的[id])
想要查询出 分类名称及其对应新闻内容的数量统计
如
name total
中国新闻 [100]
美国新闻 [200]
国际新闻 [0]
军事新闻 [2]因该怎么写?SELECT Category.name, COUNT(News.newsid) AS total FROM Category
INNER JOIN News ON News.foreignid = Category.categoryid
GROUP BY Category.name这样写的话,其中(国际新闻 [0])是不显示在查询结果内的,有什么办法让count=0的也能显示在查询结果内?
//试试
SELECT Category.name, COUNT(News.newsid) AS total FROM Category
Left JOIN News ON News.foreignid = Category.categoryid
GROUP BY Category.name
left JOIN News ON News.foreignid = Category.categoryid
GROUP BY Category.name
drop table Category
create table Category
(
categoryid int identity(1,1) primary key,
[name] varchar(8)
)
insert into Category([name]) select '中国新闻'
union all select '美国新闻'
union all select '国际新闻'
union all select '军事新闻'
select * from Category
if object_id('News','U') is not null
drop table News
create table News
(
id int identity(1,1) primary key,
title varchar(30),
foreignid int foreign key references Category(categoryid)
)
select * from News
insert into News(title,foreignid)values('中国新闻标题',1)
insert into News(title,foreignid)values('中国新闻标题',1)
insert into News(title,foreignid)values('中国新闻标题',1)
insert into News(title,foreignid)values('美国新闻标题',2)
insert into News(title,foreignid)values('美国新闻标题',2)
insert into News(title,foreignid)values('军事新闻标题',4)
insert into News(title,foreignid)values('军事新闻标题',4)
insert into News(title,foreignid)values('军事新闻标题',4)
insert into News(title,foreignid)values('军事新闻标题',4)
insert into News(title,foreignid)values('军事新闻标题',4)
--这样写的话,其中(国际新闻 [0])是不显示在查询结果内的,有什么办法让count=0的也能显示在查询结果内?
select C.name,isnull(D.totle,0)as total from Category as C
left join
(select Category.[name],count(News.foreignid)as totle from Category,News where News.foreignid=Category.categoryid
group by Category.[name])as D on C.[name]=D.[name]
/*
name total
1 中国新闻 3
2 美国新闻 2
3 国际新闻 0
4 军事新闻 5
*/
left JOIN News ON News.foreignid = Category.categoryid
GROUP BY Category.name 这种也可以呵呵
INNER JOIN News ON News.foreignid = Category.categoryid
GROUP BY Category.name
left join --即可
Inner Join 改成 Left Join
drop table Category
create table Category
(
categoryid bigint primary key identity(1,1),
name varchar(50)
)if object_id('News') is not null
drop table News
create table News
(
id bigint primary key identity(1,1),
title varchar(200),
categoryid bigint
)insert into Category values('中国新闻')
insert into Category values('美国新闻')
insert into Category values('国际新闻')
insert into Category values('军事新闻')declare @i int
declare @j int
set @i = 0
set @j = 0while(@i < 100)
begin
insert into News values('中国新闻' + cast(@i as varchar(2)),1)
set @i = @i + 1
endwhile(@j < 200)
begin
insert into News values('美国新闻' + cast(@j as varchar(2)),2)
set @j = @j + 1
endinsert into News values('军事新闻',3)select * from Category
select * from newsselect cc.name,isnull(nn.num,0) as total
from category cc
left join
(
select c.categoryid,count(c.categoryid) as num from
news n ,category c
where c.categoryid = n.categoryid group by c.categoryid
) nn
on cc.categoryid = nn.categoryid
以上代码sql2000下测试通过.怎么group by 的全是name,而不是外键?