我想执行一个操作,根据另外两列中的值的组合来聚合一列中的值,而不管它们出现在哪一列中。例如,给定此表:
a b 10.0 <-b c 5.0 c d 1.0 b a 20.0 <-
a b 10.0 <-
b c 5.0
c d 1.0
b a 20.0 <-
我想返回以下内容(如果我想做的聚合是一个总和):
a b 30.0 <-b c 5.0 c d 1.0
a b 30.0 <-
bxgwgixi1#
我们可以在bothcolumn selection和GROUP BY子句中使用LEAST和GREATEST:
GROUP BY
LEAST
GREATEST
SELECT LEAST(col1, col2) AS col1, GREATEST(col1, col2) AS col2, SUM(col3) AS sum_cFROM yourtableGROUP BY LEAST(col1, col2), GREATEST(col1, col2)ORDER BY col1, col2;
SELECT
LEAST(col1, col2) AS col1,
GREATEST(col1, col2) AS col2,
SUM(col3) AS sum_c
FROM yourtable
LEAST(col1, col2),
GREATEST(col1, col2)
ORDER BY col1, col2;
根据您的数据在此sample fiddle上试用。这里不需要CASE WHEN。
CASE WHEN
mccptt672#
我建议查看分组和排序功能,这是一个如何使用group by和order by来实现您正在尝试做的事情的示例-
SELECT CASE WHEN a <= b THEN a ELSE b END AS col1, CASE WHEN a <= b THEN b ELSE a END AS col2, SUM(c) AS sum_cFROM your_tableGROUP BY col1, col2ORDER BY col1, col2;
CASE WHEN a <= b THEN a ELSE b END AS col1,
CASE WHEN a <= b THEN b ELSE a END AS col2,
SUM(c) AS sum_c
FROM your_table
GROUP BY col1, col2
GROUP BY子句按col1和col2的唯一组合对行进行分组,ORDER BY子句按指定顺序对结果进行排序,CASE语句确保列A和B中的值排序一致,以便将“a b”和“b a”视为相同的组合,最后,SUM函数用于聚合列C中的值
col1
col2
ORDER BY
CASE
SUM
2条答案
按热度按时间bxgwgixi1#
我们可以在bothcolumn selection和
GROUP BY
子句中使用LEAST
和GREATEST
:根据您的数据在此sample fiddle上试用。
这里不需要
CASE WHEN
。mccptt672#
我建议查看分组和排序功能,这是一个如何使用group by和order by来实现您正在尝试做的事情的示例-
GROUP BY
子句按col1
和col2
的唯一组合对行进行分组,ORDER BY
子句按指定顺序对结果进行排序,CASE
语句确保列A和B中的值排序一致,以便将“a b”和“b a”视为相同的组合,最后,SUM
函数用于聚合列C中的值