问题是
消息表有这些字段
mid content
1 aa
2 bb
回复表
rid mid content
1 1 aa_reply1
2 1 aa_reply2
这两个表,现在想做成一个视图的实现结果mid mid_content reply_count
1 aa 2
2 bb 0
sql2000语句该怎么写,最好用group by谢谢了
消息表有这些字段
mid content
1 aa
2 bb
回复表
rid mid content
1 1 aa_reply1
2 1 aa_reply2
这两个表,现在想做成一个视图的实现结果mid mid_content reply_count
1 aa 2
2 bb 0
sql2000语句该怎么写,最好用group by谢谢了
on A.mid=B.mid
group by A.mid,mid_content=content
from 消息表 a
left join (select mid,count(1) as cnt
from 回复表
group by mid) b
on a.mid = b.mid
group by a.mid,a.content
from 消息表 a
left join (select mid,count(1) as cnt
from 回复表
group by mid) b
on a.mid = b.mid
更正一下
if object_id('[TA]') is not null drop table [TA]
create table [TA]([mid] int,[content] varchar(2))
insert [TA]
select 1,'aa' union all
select 2,'bb'if object_id('[TB]') is not null drop table [TB]
create table [TB]([rid] int,[mid] int,[content] varchar(9))
insert [TB]
select 1,1,'aa_reply1' union all
select 2,1,'aa_reply2'select A.mid,
mid_content=A.content,
reply_count=count(rid)
from [TA] A left join TB B on A.mid=B.mid
group by A.mid,A.content/*
mid mid_content reply_count
----------- ----------- -----------
1 aa 2
2 bb 0(所影响的行数为 2 行)
*/
drop table TA,TB
as
select
a.mid,
a.content,
count(b.rid) as reply_count
from
消息表 a
left join
回复表 b
on
a.mid=b.mid
group by
a.mid,
a.content