时间戳

首页 > 解决方案 > 组合“喜欢”的行

sql - 组合“喜欢”的行

问题描述

我有一个位置表，位置表，位置表和航班计数。我想组合其中一行出发等于另一行到达的行（例如 LA TO NY 与 NY TO LA 组合）和 SUM 类似的行。

我认为用一个例子来解释会更好。

前 

locations_from      locations_to      # of Flights
--------------------------------------------------
San Francisco, CA   Los Angeles, CA     29558
Los Angeles, CA     San Francisco, CA   32389
New York, NY        Los Angeles, CA     30389
Los Angeles, CA     New York, NY        35484
Las Vegas, NV       Los Angeles, CA     28363
Los Angeles, CA     Las Vegas, NV       34455
Honolulu, HI        Kahului, HI         46563
Kahului, HI         Honolulu, HI        16879
San Francisco, CA   New York, NY        44654
New York, NY        San Francisco, CA   25882

后 

From/To             From/To           # of Flights
---------------------------------------------------
San Francisco, CA   Los Angeles, CA      61947
New York, NY        Los Angeles, CA      65873
Las Vegas, NV       Los Angeles, CA      62818
Honolulu, HI        Kahului, HI          63442
San Francisco, CA   New York, NY         70536

我已经尝试过自己的交叉连接

where a.locations_from = b.locations_to

这行得通，但我最终得到了两倍的行（即一个用于 LA TO NY，一个用于 NY TO LA）

标签： sqltsql

解决方案

使用联合 

Declare @YourTable Table (SomeRowID int,[locations_from] varchar(50),[locations_to] varchar(50),[# of Flights] int)
Insert Into @YourTable Values 
 (1,'San Francisco, CA','Los Angeles, CA',29558)
,(2,'Los Angeles, CA','San Francisco, CA',32389)
,(3,'New York, NY','Los Angeles, CA',30389)
,(4,'Los Angeles, CA','New York, NY',35484)
,(5,'Las Vegas, NV','Los Angeles, CA',28363)
,(6,'Los Angeles, CA','Las Vegas, NV',34455)
,(7,'Honolulu, HI','Kahului, HI',46563)
,(8,'Kahului, HI','Honolulu, HI',16879)
,(9,'San Francisco, CA','New York, NY',44654)
,(10,'New York, NY','San Francisco, CA',25882);

select [locations_from], [locations_to], sum([# of Flights])
from 
(
select [locations_from], [locations_to], [# of Flights] 
from @YourTable 
where [locations_from] < [locations_to] 
union all
select [locations_to], [locations_from],  [# of Flights] 
from @YourTable 
where [locations_from] > [locations_to] 
) t
group by [locations_from], [locations_to]

推荐阅读