我有三列:发送者,接收者,#文本消息。
我需要通过获取两列的平均值来压缩行。
因此,假设卡罗尔向杰克发送了 8 条短信,杰克向卡罗尔发送了 4 条短信,结果将是这两个人之间的 6 条短信。
有关更好的描述,请参见下图。
在此处输入图像描述
在SQL中执行此操作的典型方法是:
select least(sender, receiver) as person1,
greatest(sender, receiver) as person2,
sum(num)
from t
group by least(sender, receiver),
greatest(sender, receiver);
某些数据库不支持least()
和greatest()
。 这些表达式可以替换为case
表达式。
select (case when sender < receiver then sender else receiver end) as person1,
(case when sender >= receiver then sender else receiver end) as person2,
sum(num)
from t
group by (case when sender < receiver then sender else receiver end),
(case when sender >= receiver then sender else receiver end);