求一条select语句，昨天的问题搞定！~~

Question

select
    a.*,
    isnull(select count(*) from UserOnLine where RoomId=a.RoomId),0)
from
    ChatRoom a
where
    a.RoomType=1

Answer 1

select  
    a.RoomId,
    a.RoomName,
    isnull(count(b.UserId),0)
from
    ChatRoom a
left join
    UserOnLine b
on
    a.RoomId=b.RoomId 
group by
    a.RoomId,a.RoomName
这是昨天有人帮我写的
今天我在上面加了一个条件：ChatRoom中的RoomType='1'

Answer 2

select
    a.RoomNO,
    a.RoomName,
    isnull((select count(*) from UserOnLine where RoomId=a.RoomId),0)
from
    ChatRoom a
where
    a.RoomType=1

Answer 3

select  
    a.RoomId,
    a.RoomName,
    isnull(count(b.UserId),0)
from
    ChatRoom a
left join
    UserOnLine b
on
    a.RoomId=b.RoomId 
where
    a.RoomType=1
group by
    a.RoomId,a.RoomName

Answer 4

select a.RoomId,a.RoomName,count(distinct UserId) 在线人数  from ChatRoom a,UserOnLine b where b.RoomType=1 and a.RoomId=b.RoomId   group by a.RoomId,a.RoomName

Answer 5

libin_ftsafe(子陌红尘)太厉害了！~
简直是我的偶像呀！~太谢谢了！~~~

Answer 6

回复人： xueguang(xg) ( ) 信誉：100  2005-11-18 13:45:00  得分: 0  
 
 
   
select a.RoomId,a.RoomName,count(distinct UserId) 在线人数  from ChatRoom a,UserOnLine b where b.RoomType=1 and a.RoomId=b.RoomId   group by a.RoomId,a.RoomName
  
 
这条要是UserOnLine中没数据的话，就没有结果了~~

Answer 7

select roomno,roomname,cound(userid) from
(
select a.roomno,a.roomname,b.userid from chatroom a inner join useronline b on
a.roomno=b.room where a.roomtype=1
) 
a
group by roomno,roomname
orderby roomno

Answer 8

select roomno,roomname,cound(userid) from
(
select a.roomno,a.roomname,b.userid from chatroom a inner join useronline b on
a.roomno=b.room where a.roomtype=1
) 
a
group by roomno,roomname
orderby roomno
这个的效率会不会很低哦？