Início / dba / Perguntas / 182693
Accepted
Peter
Asked: 2017-08-05 06:02:22 +0800 CST

Ordenação de linhas com base na referência cruzada entre colunas

  • 772

Tenho uma tabela assim:

Lat1 Lng1 Lat2 Lng2
---- ---- ---- ----
1 1 2 2
2 2 5 5
3 3 4 4
4 4 3 3
5 5 4 4

É possível retornar as linhas na seguinte ordem?

Lat1 Lng1 Lat2 Lng2
---- ---- ---- ----
1 1 2 2
2 2 5 5
5 5 4 4
4 4 3 3
3 3 4 4

Existe uma rowidcoluna única.

Os pares (Lat1,Lng1)e (Lat2,Lng2)sempre serão únicos.

lat1e long1de linha n+1deve ser igual a lat2e long2de linha n.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Você pode fazer isso com um CTE recursivo hierárquico.

    O único problema é que você tem um loop em seus dados, devido a haver 3,3, 4,4e 4,4, 3,3.

    drop table #tmp;

create table #tmp
(
lat1 int,
lng1 int,
lat2 int,
lng2 int
);

insert into #tmp values (1,1,2,2);
insert into #tmp values (2,2,5,5);
insert into #tmp values (3,3,4,4);
insert into #tmp values (4,4,3,3);
insert into #tmp values (5,5,4,4);

WITH cte AS
(
  SELECT 
   lat1,lng1,lat2,lng2,
    0 AS level, cast('('+cast(lat1 as varchar) + ',' + cast(lng1 as varchar)+')' as varchar) as path 
  FROM #tmp
  WHERE lat1=1 and lng1=1
  UNION ALL
  SELECT 
  t.lat1,t.lng1,t.lat2,t.lng2, 
  level+1, cast(path+','+'('+cast(t.lat2 as varchar) + ',' + cast(t.lng2 as varchar)+')' as varchar)
  FROM #tmp t
  INNER JOIN cte c
    ON c.lat2 = t.lat1 and c.lng2 = t.lng1
)
select * from cte; 
;

    A WHERE lat1=1 and lng1=1parte especifica a base da hierarquia (que provavelmente é o início de uma jornada, neste caso).

    • 3

  2. Experimente meu script com dados de amostra diff.

    Eu não acho que você precisa Recursive CTEfazer isso.

    Funciona emSql server 2012+

    create table #tmp
(
lat1 int,
lng1 int,
lat2 int,
lng2 int
);

insert into #tmp values (1,1,2,2);
insert into #tmp values (2,2,5,5);
insert into #tmp values (3,3,4,4);
insert into #tmp values (4,4,3,3);
insert into #tmp values (5,5,4,4);

;with CTE as
(
select t.lat1,t.lng1, t.lat2,t.lng2
,ROW_NUMBER()over(order by (select NULL))rn 
from #tmp t
)
select isnull(lag(lat2,1) over(order by rn),lat1)lat1,  
isnull(lag(lng2,1) over(order by rn),lng1)lng1,  
c.lat2,c.lng2
from cte c


drop table #tmp;

    Observe também CTEque row_numberpode ou não exigir na vida real, dependendo dos dados reais. É apenas para orderingfins.

    • 1

relate perguntas