时间戳

首页 > 解决方案 > 两条记录相同时的单行提取

sql - 两条记录相同时的单行提取

问题描述

Fromjourney ToJourney   KM
Delhi       Mumbai      1000
Mumbai      Delhi       1000
Gurgaon     Noida       50
Noida       Gurgaon     50
Ghaziabad   Faridabad   60
Faridabad   Ghaziabad   60
Delhi       Meerut      80
Meerut      Delhi       80

我有三列from,To和KM.

当 From To 与 From-Mumbai、To-Delhi To-Delhi、From-Mumbai 相同的城市时，我想要单行数据

Ι 想要单行这个数据

标签： sql

解决方案

select T1.Fromjourney,T1.ToJourney
  ,T2.*
from TestTable T1
left join TestTable T2 
  on T1.Fromjourney = T2.ToJourney
  and T1.ToJourney = T2.Fromjourney
  and T1.Fromjourney < T2.Fromjourney 
where T2.Fromjourney is not null

| Fromjourney | ToJourney | Fromjourney | ToJourney |   KM |
|-------------|-----------|-------------|-----------|------|
|       Delhi |    Mumbai |      Mumbai |     Delhi | 1000 |
|     Gurgaon |     Noida |       Noida |   Gurgaon |   50 |
|   Faridabad | Ghaziabad |   Ghaziabad | Faridabad |   60 |
|       Delhi |    Meerut |      Meerut |     Delhi |   80 |

演示 - SQL Fiddle

推荐阅读