There is a database with cities and their coordinates. It is required to display line by line distances between all cities WITHOUT repetitions.

Approximate base

CITY_NAME COORD_X COORD_Y City1 5 10 City2 20 30 City3 50 70

The distance, for example, is calculated using the formula COORD_X1 - COORD_X2 . Similarly for COORD_Y.

Note that the line with the distance from City1 to City2 is the SAME line, as is the distance from City2 to City1 . Those. it will not be necessary to display it.

Actually at the output of d. result:

 City1-City2 15 20 City1-City3 45 60 City2-City3 30 40
  • 2
    Very interesting. What have you done? - Igor
  • 2
    And me, and me :) I also wonder what you have already done? :) - Max ZS
  • I can prompt the decision on the Python. It will be necessary - please contact) - TitanFighter
  • 6
    Everyone with each one is a Cartesian product (aka cross join in SQL), you can cut off repetition and do it yourself for City1 < City2 , the rest, I think, figure it out. - i-one
  • one
    @Igor I did a little SELECT c1. [CITY_NAME], c2. [City_name], abs (c2. [COORD_X] - c1. [COORD_X]) as Dist_X, abs (c2. [COORD_Y] - c1. [COORD_Y]) as Dist_Y, row_number () over (partition BY c2. [CITY_NAME] ORDER BY c1. [CITY_NAME]) as num FROM [dbo]. [CITIES] as c1 join cities as c2 on c1.CITY_NAME <> c2.CITY_NAME and c2. CITY_NAME <> c1.CITY_NAME order by c1.CITY_NAME Row_Number has already been added at the very last moment. - DmitryKh

1 answer 1

I do not have your DBMS, so the answer is in pure SQL (you will have to adapt it to your dialect). In addition, to use this solution, you will need to enter a primary numeric key in the CITIES table (if it does not already exist). The point is to use CROSS JOIN, but to compare primary numeric keys with the help of the inequality sign (it does not matter more or less here). Then in the resulting Cartesian matrix, we take not all the values, but only a triangle under or above the diagonal.

 SELECT c1.CITY_NAME, c2.CITY_NAME, SQRT(ABS(c2.COORD_X - c1.COORD_X)) + SQRT(ABS(c2.COORD_Y - c1.COORD_Y)) as Dist_Y FROM CITIES AS c1 CROSS JOIN CITIES AS c2 WHERE c1.id < c2.id
  • You're right. I blinked on a simple JOIN, and in CROSS (in my query) I set an additional condition, which gave an incorrect result. The only thing I corrected in your code is WHERE c1.CITY_NAME <c2.CITY_NAME - DmitryKh
  • @DmitryKh A normal join with the same condition should give the same result. (They differ only in selectable columns in the SQL standard. And in many DBMS, including mysql, they are generally synonymous). You would try, in the form of a query in the text of the question would write, you would help in about 5 minutes. At least 15 minutes later, they wrote that it was necessary to use city1 <city2, and it took so long because the community decided not to answer the text because it was too much like a learning task without the author trying to make an independent decision - Mike
  • @Mike My wrong version with the usual JOIN I gave below the post. He did not give a similar result. I agree, the written code did not immediately lead. The community was misled :) - DmitryKh
  • @Mike c1.CITY_NAME <c2.CITY_NAME instead of c1.CITY_NAME <> c2.CITY_NAME But why did the less sign work? How would some sorting of values ​​work? Off Why 4 spaces before the code do not give a footnote when formatting? - DmitryKh
  • @DmitryKh In comments, the code only in reverse apostrophes works. or are you talking about? And the sign is less ... three entries are given 1, 2, 3. do join. If <> then we get 12, 13, 21, 31, 23, 32. And with less (or more) only 12, 13, 23 the rest of the condition does not pass - Mike