现有2个表, user, msg
user表:userid, username
msg表:msgid, userid, content
现要统计每个用户有多少条信息
例如:user表
1 csdn1
2 csdn2
msg表:
1 1 sssssss
2 1 ttttttttt
3 1 fjdsklfds
4 2 jsfdlkafdsl
我想要的结果是:
userid, username, count
1 csdn1 3 (在msg表中有3条记录)
2 csdn2 1
用一条sql语句可以实现吗?
9 个解决方案
#1
select *,[count]=(select count(*) from msg where userid=a.userid)
from user a
#2
楼上的在Access下出错了,说最少有一个字段未提供值。。。
#3
select a.*,[count]=count(*)再不行去相关版块问问。
from user a,msg b where a.userid=b.userid
group by a.userid,a.username
#4
create table [user]
(
userid int NULL,
username nvarchar(50) NULL
)
create table msg
(
id int null,
userid int NULL,
commend nvarchar(50) NULL
)
insert into [user]
select 1,'csdn1'
insert into [user]
select 2,'csdn2'
insert into msg
select 1,1,'sssssss'
insert into msg
select 2,1,'sssssss'
insert into msg
select 3,2,'sssssss'
insert into msg
select 4,2,'sssssss'
select TM.userid,TU.username, COUNT(1) AS RECORDCOUNT from msg TM
LEFT JOIN [user] TU ON TM.userid=TU.userid
group by TM.userid,TU.username
userid username RECORDCOUNT
----------- -------------------------------------------------- -----------
1 csdn1 2
2 csdn2 2
(2 行受影响)
#5
在msg中没记录的用户会显示出来吗?
#6
select a.userid,a.username,[count]=count(*)
from user a join msg b on a.userid=b.userid
group by a.userid,a.username
#7
我实际应用的表中有很多个字段,按这种写法会不会很麻烦?
#8
如果要出所有user的信息就把left转成right
#9
ACCESS的直接去其他数据库版问问
#1
select *,[count]=(select count(*) from msg where userid=a.userid)
from user a
#2
楼上的在Access下出错了,说最少有一个字段未提供值。。。
#3
select a.*,[count]=count(*)再不行去相关版块问问。
from user a,msg b where a.userid=b.userid
group by a.userid,a.username
#4
create table [user]
(
userid int NULL,
username nvarchar(50) NULL
)
create table msg
(
id int null,
userid int NULL,
commend nvarchar(50) NULL
)
insert into [user]
select 1,'csdn1'
insert into [user]
select 2,'csdn2'
insert into msg
select 1,1,'sssssss'
insert into msg
select 2,1,'sssssss'
insert into msg
select 3,2,'sssssss'
insert into msg
select 4,2,'sssssss'
select TM.userid,TU.username, COUNT(1) AS RECORDCOUNT from msg TM
LEFT JOIN [user] TU ON TM.userid=TU.userid
group by TM.userid,TU.username
userid username RECORDCOUNT
----------- -------------------------------------------------- -----------
1 csdn1 2
2 csdn2 2
(2 行受影响)
#5
在msg中没记录的用户会显示出来吗?
#6
select a.userid,a.username,[count]=count(*)
from user a join msg b on a.userid=b.userid
group by a.userid,a.username
#7
我实际应用的表中有很多个字段,按这种写法会不会很麻烦?
#8
如果要出所有user的信息就把left转成right
#9
ACCESS的直接去其他数据库版问问