呵呵不好意思,忘了category表!user表,假设只有一列:nameyang
wang
licategory表,假设只有一列:
namemath
english
computer
mail
id category toUser fromUser
001 computer li wang
002 math wang yang
003 english wang li
004 english yang li
005 math li yang
006 computer yang wang
007 computer wang yang
....求的表,是每个人在每一类发的数量(sendcount),和收的数量(receCount)user category sendCount receCount
wang math 1 1
wang english 0 1
wang computer 2 1
... ...
wang
licategory表,假设只有一列:
namemath
english
computer
id category toUser fromUser
001 computer li wang
002 math wang yang
003 english wang li
004 english yang li
005 math li yang
006 computer yang wang
007 computer wang yang
....求的表,是每个人在每一类发的数量(sendcount),和收的数量(receCount)user category sendCount receCount
wang math 1 1
wang english 0 1
wang computer 2 1
... ...
(select user.name as na,mail.category as ca,mail.touser,mail.fromuser
from mail,user
where mail.id = user.mid ) as b
group by b.na,b.ca
order by 1,2因为刚才自己虚拟表,随意去的表名字,可能有点乱,大概这样吧,看行不行
if objectproperty(object_id(N'[dbo].[usertable]'),N'IsUserTable')=1
drop table usertable
go
create table usertable(id char(4),name char(10))
insert usertable select '0001','张三'
union all select '0002','李四'
union all select '0003','王五'
union all select '0004','赵六'if objectproperty(object_id(N'[dbo].[mail]'),N'IsUserTable')=1
drop table mail
gocreate table mail(id char(4),category char(10),toUser char(4),fromUser char(4))
insert mail select '0001','分类一','0003','0001'
union all select '0002','分类二','0004','0002'
union all select '0001','分类一','0002','0001'
union all select '0004','分类二','0003','0004'--SQLselect 用户=b.name,分类=a.category,收信量=sum(收信量),发信量=sum(发信量)
from
(select id=toUser,category,收信量=0,发信量=count(*) from mail group by toUser,category union all
select id=fromUser,category,收信量=count(*),发信量=0 from mail group by fromUser,category) a
left join usertable b on a.id=b.id
group by b.name,a.category
order by b.name,category --删除测试表
drop table usertable
drop table mail
left join (select Count(*) Count,category,toUser UserName From mail group by category,toUser) c on a.username=c.username and b.category=c.category
left join (select Count(*) Count,category,FromUser UserName From mail group by category,FromUser) d on a.username=d.username and b.category=d.category
试试看
left join (select Count(*) Count,category,toUser UserName From mail group by category,toUser) c on a.username=c.username and b.category=c.category
left join (select Count(*) Count,category,FromUser UserName From mail group by category,FromUser) d on a.username=d.username and b.category=d.category
order by 1,2
(SELECT COUNT(*)
FROM mail
WHERE category = b.name AND toUser= a.name) AS toUser,
(SELECT COUNT(*)
FROM mail
WHERE category = b.name AND fromUser= a.name) AS fromUser
FROM user a CROSS JOIN
category b