呵呵不好意思,忘了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 ... ...
select fromuser,category,fc,tc from (select fromuser,category,count(1) fc from mail group by fromuser,category) t full outer join (select touser,category,count(1) tc from mail group by touser,category) tt on (t.fromuser=tt.fromuser and t.category=tt.category);
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
... ...
(select fromuser,category,count(1) fc from mail group by fromuser,category) t
full outer join
(select touser,category,count(1) tc from mail group by touser,category) tt
on (t.fromuser=tt.fromuser and t.category=tt.category);