sql - 如何在SQL中的两列上交叉分组？

Question

我的网站上有一个个人消息系统，这很容易。
但我想要一个管理员页面，其中显示用户之间的所有对话及其消息量。
所以表格看起来像(简化版):

CREATE TABLE pm (
    id        INT(10)     NOT NULL AUTO_INCREMENT,
    from      INT(10)     NOT NULL REFERENCES (usertable),
    to        INT(10)     NOT NULL REFERENCES (usertable),
    message   BLOB        NOT NULL
);

例子:
假设我有一些用户:Mark、John、Bryan 和 Kate。
Mark (from) 向 John (to) 发送 5 条消息，John (from) 向 Mark (to) 发送 3 条消息。
Kate (from) 向 Bryan (to) 发送 2 条消息，Bryan (from) 向 Kate (to) 发送 1 条消息。
我想要一个结果集，显示

Mark - John - 8 messages

Kate - Bryan - 3 messages

这一次适用于我表中的所有用户。
我真的坚持这一点，我到处搜索，但没有找到解决方案。
困难在于我希望列出所有用户，而且我必须以某种方式跨越“from”和“to”列......
我希望任何人都能够提供帮助。
提前致谢。

Answer 1

select from_id, to_id, count(*) count_between
from
(
    select from_id, to_id from pm
    union all
    select to_id, from_id from pm
) combined
where from_id < to_id
group by from_id, to_id

完整 sample

CREATE TABLE pm (from_id int,to_id int);
insert pm select 1,2;
insert pm select 1,2;
insert pm select 1,2;
insert pm select 1,2;
insert pm select 1,2;
insert pm select 2,1;
insert pm select 2,1;
insert pm select 2,1;
insert pm select 3,4;
insert pm select 3,4;
insert pm select 4,3;

select from_id, to_id, count(*) count_between
from
(
    select from_id, to_id from pm
    union all
    select to_id, from_id from pm
) combined
where from_id < to_id
group by from_id, to_id

--- results
from_id     to_id       count_between
----------- ----------- -------------
1           2           8
3           4           3

要将 ID 转换为名称，请使用正常的 user table 之类的。例如

select u1.name from_, u2.name to_, count(*) count_between
from
(
    select from_id, to_id from pm
    union all
    select to_id, from_id from pm
) combined
join users u1 on u1.id = combined.from_id
join users u2 on u2.id = combined.to_id
where from_id < to_id
group by u1.name, u2.name