时间戳

首页 > 解决方案 > SQL 计算两列中的元素

sql - SQL 计算两列中的元素

问题描述

我正在尝试提出一个查询，该查询将允许我计算同一个表的两列的值。例如

table: rider_geo_addresses
==================================
|id  | origin_id | destination_id |
==================================
 1        4             8
 1        2             6
 1        8             4
 1        6             2
 1        1             5
 1        2             8

我想要一个查询，它会给我 origin_id 和 destination_id 组合排序后的计数

table: result
============================
| geo_addresses  | counter |
============================
       8              3
       2              3
       6              2
       4              2
       5              1
       1              1

我试图在两个单独的表上获取值并使用联合来组合它们，但它没有考虑到可能出现在两列中的相同值。这是我试过的

select ( origin_id) as geo_addresses, count(origin_id) as counter
from rider_geo_addresses
where rider_profile_id
group by  origin_id
union
select ( destination_id) as geo_addresses, count(destination_id) as counter
from rider_geo_addresses
where rider_profile_id
group by  destination_id
order by counter desc;

标签： sqlpostgresql

解决方案

据我了解，您的解决方案是在工会之后进行分组

SELECT 
  geo_addresses
  ,count(*) as counter 
FROM (
   select origin_id as geo_addresses
   from rider_geo_addresses
   where rider_profile_id
   union all
   select destination_id as geo_addresses
   from rider_geo_addresses
   where rider_profile_id
) t
GROUP BY geo_addresses
ORDER BY counter desc

推荐阅读