如何从两列中获取双向组合的计数？ [英] How to get count of two-way combinations from two columns?

问题描述

我在一家货运公司工作，我们有兴趣了解我们其中一辆卡车在两个城市之间往任一方向行驶的次数。

I work for a trucking company, and we're interested in getting a count of the number of times one of our trucks travels between two cities, in either direction.

我有一张表格，列出了每个旅程段的出发地和目的地，例如：

I have a table that lists an origin and destination for each trip segment, such as:

Origin    Destination

City 1    City 2
City 2    City 1
City 3    City 4
City 2    City 1

我需要一个查询，告诉我在城市1和城市2之间有3次旅行，在城市3和城市4之间有1次旅行。

I need a query that tells me that there were three trips between City 1 and City 2 and one trip between City 3 and City 4. Thanks very much!

推荐答案

我认为以下应该可以解决问题。

I think the following should do the trick.

SELECT route , COUNT(1) FROM 
(
   SELECT 
   CASE WHEN Origin > Destination THEN Origin+'_'+Destination
   ELSE Destination+'_'+Origin 
   END AS route
   FROM table1
 )a
 GROUP BY route

这篇关于如何从两列中获取双向组合的计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！