遇到了这样一个问题,试了N久还是没解决于是乎放到这里来求助下。希望各们大侠出招相助。先谢谢了...……^_^现有以下两个表,用户名是不能重复的。问题:统计出每个用户日志被评论的次数,同一编日志被同一个用户评论多次的只算一次。以下结果应该为:woody 的日志被评论次数为2;jon的日志被评论的次数为2
user表
(自动编号) 用户名 用户地址 等用户信息
userid username addr mail
1 woody a [email protected]
2 jon b [email protected]
3 tom c [email protected]表(用户日志评论表)
(自动编号) 用户日志ID 写日志的用户名 评论内容 评论人名字
ID mainid username commnet comm_user
1 1 woody *1 jon
2 1 woody *2 jon
3 1 woody *3 tom
4 2 jon * woody
5 3 jon * tom
user表
(自动编号) 用户名 用户地址 等用户信息
userid username addr mail
1 woody a [email protected]
2 jon b [email protected]
3 tom c [email protected]表(用户日志评论表)
(自动编号) 用户日志ID 写日志的用户名 评论内容 评论人名字
ID mainid username commnet comm_user
1 1 woody *1 jon
2 1 woody *2 jon
3 1 woody *3 tom
4 2 jon * woody
5 3 jon * tom
from comment
group by 写日志的用户名
sum(A.ccount) as 评论次数
FROM
(SELECT mainid,
count(DISTINCT comm_user) as ccount
FROM comment
GROUP BY mainid
)
A
LEFT JOIN (SELECT DISTINCT mainid, username FROM comment)
B
ON A.mainid = B.mainid
GROUP BY B.username
from comment
group by username
create table user表
(
userid int,
username varchar(10),
addrmail varchar(10)
)
insert into user表(userid,username,addrmail) values(1,'woody','[email protected]')
insert into user表(userid,username,addrmail) values(2,'jon' ,'[email protected]')
insert into user表(userid,username,addrmail) values(3,'tom' ,'[email protected]')create table comment表
(
ID int,
mainid int,
username varchar(10),
commnet varchar(10),
comm_user varchar(10)
)
insert into comment表(ID,mainid,username,commnet,comm_user) values(1,1,'woody','*1','jon')
insert into comment表(ID,mainid,username,commnet,comm_user) values(2,1,'woody','*2','jon')
insert into comment表(ID,mainid,username,commnet,comm_user) values(3,1,'woody','*3','tom')
insert into comment表(ID,mainid,username,commnet,comm_user) values(4,2,'jon' ,'*' ,'woody')
insert into comment表(ID,mainid,username,commnet,comm_user) values(5,3,'jon' ,'*' ,'tom')
go
--写语句
select username , count(*) as 评论次数 from
(
select distinct username , comm_user from comment表
) m
group by username --删除表
drop table user表
drop table comment表username 评论次数
---------- -----------
jon 2
woody 2(所影响的行数为 2 行)
create table user表
(
userid int,
username varchar(10),
addrmail varchar(10)
)
insert into user表(userid,username,addrmail) values(1,'woody','[email protected]')
insert into user表(userid,username,addrmail) values(2,'jon' ,'[email protected]')
insert into user表(userid,username,addrmail) values(3,'tom' ,'[email protected]')create table comment表
(
ID int,
mainid int,
username varchar(10),
commnet varchar(10),
comm_user varchar(10)
)
insert into comment表(ID,mainid,username,commnet,comm_user) values(1,1,'woody','*1','jon')
insert into comment表(ID,mainid,username,commnet,comm_user) values(2,1,'woody','*2','jon')
insert into comment表(ID,mainid,username,commnet,comm_user) values(3,1,'woody','*3','tom')
insert into comment表(ID,mainid,username,commnet,comm_user) values(4,2,'jon' ,'*' ,'woody')
insert into comment表(ID,mainid,username,commnet,comm_user) values(5,3,'jon' ,'*' ,'tom')
go
--写语句
select user表.username , isnull(b.评论次数,0) as 评论次数 from user表 left join
(
select username , count(*) as 评论次数 from
(
select distinct username , comm_user from comment表
) m
group by username
) b on user表.username = b.username--删除表
drop table user表
drop table comment表username 评论次数
---------- -----------
woody 2
jon 2
tom 0(所影响的行数为 3 行)
如要再select出用户的其它相关信息,后面的group by username 就出问题了!
如下(有问题):
select username ,ADDR,MAIL, count(*) as 评论次数 from
(
select distinct username , comm_user from comment表
) m
group by username
create table user表
(
userid int,
username varchar(10),
addrmail varchar(10)
)
insert into user表(userid,username,addrmail) values(1,'woody','[email protected]')
insert into user表(userid,username,addrmail) values(2,'jon' ,'[email protected]')
insert into user表(userid,username,addrmail) values(3,'tom' ,'[email protected]')create table comment表
(
ID int,
mainid int,
username varchar(10),
commnet varchar(10),
comm_user varchar(10)
)
insert into comment表(ID,mainid,username,commnet,comm_user) values(1,1,'woody','*1','jon')
insert into comment表(ID,mainid,username,commnet,comm_user) values(2,1,'woody','*2','jon')
insert into comment表(ID,mainid,username,commnet,comm_user) values(3,1,'woody','*3','tom')
insert into comment表(ID,mainid,username,commnet,comm_user) values(4,2,'jon' ,'*' ,'woody')
insert into comment表(ID,mainid,username,commnet,comm_user) values(5,3,'jon' ,'*' ,'tom')
go
--写语句
select user表.userid , user表.username , user表.addrmail , isnull(b.评论次数,0) as 评论次数 from user表 left join
(
select username , count(*) as 评论次数 from
(
select distinct username , comm_user from comment表
) m
group by username
) b on user表.username = b.username--删除表
drop table user表
drop table comment表userid username addrmail 评论次数
----------- ---------- ---------- -----------
1 woody [email protected] 2
2 jon [email protected] 2
3 tom [email protected] 0(所影响的行数为 3 行)
谢谢啦!