sql - 如何在 SQL 中将 2 个不同列的总和作为一个列？

Question

我有一张这样的桌子-

Product | Competitor | Comparison Count
 A           B            3
 C           D            5
 B           A            2
 E           A            1

我需要产品 A 的输出，它的竞争对手和这样的计数 -

Product | Competitor | Comparison Count
 A           B           5
 A           E           1

我正在使用 postgresql，当产品 A 出现在上面的产品和竞争对手列上时，我对如何汇总产品 A 的比较计数一无所知。任何线索将不胜感激。谢谢！

标签： sqlpostgresql

Answer 1

在选择中使用条件列。在我们正在搜索的示例中B，数据有点扩展：

with my_table(product, competitor, comparison_count) as (
values
    ('A', 'B', 3),
    ('C', 'D', 5),
    ('B', 'A', 2),
    ('B', 'D', 2),
    ('E', 'A', 1),
    ('D', 'B', 4)
)

select
    case when product = 'B' then product else competitor end as product,
    case when product = 'B' then competitor else product end as competitor,
    sum(comparison_count) as comparison_count
from my_table
where product = 'B' or competitor = 'B'
group by 1, 2
order by 2

 product | competitor | comparison_count 
---------+------------+------------------
 B       | A          |                5
 B       | D          |                6
(2 rows)