统计用户发贴数
表一user
uid uname
1 'aaa'
2 'bbb'
3 'cccc'
表二 topic
id uid title
1 1 'asfsa'
2 2 'xcvxv'
3 2 'hhhh'
4 2 'sdfs'统计 用户主题数想返回如下结果 按主题数降序
uid uname cnt
2 'bbb' 3
1 'aaa' 1或者
uid uname cnt
2 'bbb' 3
1 'aaa' 1
3 'ccc' 0
表一user
uid uname
1 'aaa'
2 'bbb'
3 'cccc'
表二 topic
id uid title
1 1 'asfsa'
2 2 'xcvxv'
3 2 'hhhh'
4 2 'sdfs'统计 用户主题数想返回如下结果 按主题数降序
uid uname cnt
2 'bbb' 3
1 'aaa' 1或者
uid uname cnt
2 'bbb' 3
1 'aaa' 1
3 'ccc' 0
insert into [user] values(1 , 'aaa' )
insert into [user] values(2 , 'bbb' )
insert into [user] values(3 , 'cccc')
create table topic(id int,uid int,title varchar(10))
insert into topic values(1 , 1 , 'asfsa' )
insert into topic values(2 , 2 , 'xcvxv' )
insert into topic values(3 , 2 , 'hhhh' )
insert into topic values(4 , 2 , 'sdfs' )
goselect t.* , isnull((select count(1) from topic where uid = t.uid ),0) cnt from [user] t--drop table [user] , topic/*
uid uname cnt
----------- ---------- -----------
1 aaa 1
2 bbb 3
3 cccc 0(所影响的行数为 3 行)
*/
create table [user](uid int , uname varchar(10))
insert into [user] values(1 , 'aaa' )
insert into [user] values(2 , 'bbb' )
insert into [user] values(3 , 'cccc')
create table topic(id int,uid int,title varchar(10))
insert into topic values(1 , 1 , 'asfsa' )
insert into topic values(2 , 2 , 'xcvxv' )
insert into topic values(3 , 2 , 'hhhh' )
insert into topic values(4 , 2 , 'sdfs' )
go--用子查询的。
select t.* , isnull((select count(1) from topic where uid = t.uid ),0) cnt from [user] t order by cnt desc
/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1
3 cccc 0(所影响的行数为 3 行)
*/--用连接的。
select m.* , isnull(n.cnt,0) cnt
from [user] m left join
(select uid , count(1) cnt from topic group by uid) n
on m.uid = n.uid
order by cnt desc
/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1
3 cccc 0(所影响的行数为 3 行)
*/drop table [user] , topic
/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1(所影响的行数为 2 行)
*/
你自己选择吧.create table [user](uid int , uname varchar(10))
insert into [user] values(1 , 'aaa' )
insert into [user] values(2 , 'bbb' )
insert into [user] values(3 , 'cccc')
create table topic(id int,uid int,title varchar(10))
insert into topic values(1 , 1 , 'asfsa' )
insert into topic values(2 , 2 , 'xcvxv' )
insert into topic values(3 , 2 , 'hhhh' )
insert into topic values(4 , 2 , 'sdfs' )
go--用子查询的。
select t.* , isnull((select count(1) from topic where uid = t.uid ),0) cnt from [user] t order by cnt desc
/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1
3 cccc 0(所影响的行数为 3 行)
*/--用连接的。
select m.* , count(1) cnt
from [user] m inner join topic n
on m.uid = n.uid
group by m.uid,m.uname
order by cnt desc
/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1(所影响的行数为 2 行)
*/drop table [user] , topic
/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1(所影响的行数为 2 行)
*/用:
select m.* , count(1) cnt
from [user] m inner join topic n
on m.uid = n.uid
group by m.uid,m.uname
order by cnt desc如果你要:/*
uid uname cnt
----------- ---------- -----------
2 bbb 3
1 aaa 1
3 cccc 0(所影响的行数为 3 行)
*/需要下面两种中的一种
select t.* , isnull((select count(1) from topic where uid = t.uid ),0) cnt from [user] t order by cnt descselect m.* , isnull(n.cnt,0) cnt
from [user] m left join
(select uid , count(1) cnt from topic group by uid) n
on m.uid = n.uid
order by cnt desc不管哪种结果或方法,都建议对两表uid建立索引