select
a.name,
b.title,
renote = (select isnull(count(*),0) from answer where origid = b.id)
from
people a,
note b
where
a.id = b.writerid
a.name,
b.title,
renote = (select isnull(count(*),0) from answer where origid = b.id)
from
people a,
note b
where
a.id = b.writerid
解决方案 »
- 数据库能直接存入xml文件吗
- sql server 2005 express 下生成的mdf文件如何附加到sql server2000
- 如何给procedure改名字?
- 50分@@@搞了半天,无解,字符转换为日期 CONVERT( @date+' 23:59:59' , datetime))
- 自定义 排序问题
- 三天都没有解决,只能请专家出马了,为何这段存储过程执行不稳定?解决了另开帖送分
- sql 语句该如何写?
- 本人刚接触编程...看到CSDN有好多高手!请问???
- as400 rpg问题到哪里去问?谁有好site?
- 如何根据生效日期获取相应的单价
- 一个sql查询赋值给字符串的问题
- 查询结果集的处理,请高手指导,在线等。。。。
,A.title as '标题'
,C.num as '回复数'
from note A
join people B on A.writerid=B.id
left join (
select origid,count(1) as 'num'
from answer
group by origid
)C on C.origid=A.id
,A.title as '标题'
,isnull(C.num,0) as '回复数'
from note A
join people B on A.writerid=B.id
left join (
select origid,count(1) as 'num'
from answer
group by origid
)C on C.origid=A.id
create table people(id int,[name] varchar(20))
insert into people select 1,'buman'
insert into people select 2,'regretwang2000'
insert into people select 3,'sun'
insert into people select 4,'sunwang81081'create table note(id int,title varchar(20),content varchar(20),writerid int)
insert into note select 1,'shsh','shshsh',1
insert into note select 2,'sss' ,'ssss' ,1
insert into note select 3,'dddd','ddddd' ,2
insert into note select 4,'ddd' ,'dd' ,1create table answer(id int,answercontent varchar(20),origid int)
insert into answer select 1,'sssssssssss',1
insert into answer select 2,'sss',1
insert into answer select 3,'wwwwwwwwwww',1
--执行查询
select
a.name,
b.title,
renote = (select isnull(count(*),0) from answer where origid = b.id)
from
people a,
note b
where
a.id = b.writerid
--输出结果
/*
name title renote
-------------- ------ -------
buman shsh 3
buman sss 0
regretwang2000 dddd 0
buman ddd 0
*/