SQL函数来选择在所有4个季度中发生的事件

Question

我正试图解决一个问题，该问题涉及分析一段时间内的机票数据。以下是表格的简短版本：

Quarter city 1 city 2
1 Albany，NY Orlando，FL

问题：如何只显示城市1和城市2的独特组合，这些组合发生在所有4个季度？在这个例子中，我唯一希望回归的是纽约州奥尔巴尼和佛罗里达州奥兰多，因为其他选择并没有在所有方面重复。

我有以下命令，只显示city1和city2的唯一组合，但后来我被卡住了！感谢帮助！

select city1, city2 FROM airfare_data
UNION
SELECT city1, city2 from airfare_data
ORDER BY city1, city2;

Answer 1

您可以尝试按城市分组，并从city1和city2 之间的联合中计算不同的季度

select city 
from (
select quarter, city1 city
from mytable  
union 
select quarter, city2
from mytable )   
group by  city 
having count(distinct quarter)  = 4

Answer 2

您可以group by city1, city2并在having子句中设置条件：

select city1, city2
from airfare_data
group by city1, city2
having count(distinct Quarter) = 4;

如果两个城市的顺序无关紧要，那么您可以使用函数min()和max()，如下所示：

select min(city1, city2) city1, max(city1, city2) city2 
from airfare_data
group by min(city1, city2), max(city1, city2)
having count(distinct Quarter) = 4;

请参阅演示
结果：

| city1     | city2      |
| --------- | ---------- |
| Albany NY | Orlando FL |

Answer 3

测试数据

Select 1 as Quarter, 'Albany, NY' as city1, 'Orlando, FL' as city2
into #AirData
Union
Select 2, 'Albany, NY', 'Orlando, FL' Union
Select 3, 'Albany, NY', 'Orlando, FL' Union
Select 4, 'Albany, NY', 'Orlando, FL' Union
Select 4, 'Albany, NY', 'Los Angeles, CA (Metropolitan Area)' Union
Select 4, 'Albany, NY', 'Washington, DC (Metropolitan Area)' Union
Select 2, 'Albany, NY', 'Atlanta, GA (Metropolitan Area)' Union
Select 3, 'Albany, NY', 'Atlanta, GA (Metropolitan Area)' Union
Select 1, 'Albany, NY', 'Atlanta, GA (Metropolitan Area)' Union
Select 4, 'Albany, NY', 'Atlanta, GA (Metropolitan Area)' 

查询为：

select city1, city2
from #AirData 
group by  city1, city2 
having count(distinct quarter)  = 4