如何在Cognos中创建重复循环表以计算地点间距离？

你要的笛卡尔积结构完全可以通过**自连接（交叉连接）**实现，不需要用Union。核心思路是把同一张表当作两个独立的表来关联，让每个地点都和所有地点（包括自身）配对。

假设你的表名为locations，直接用下面的SQL就能得到预期结果：

SELECT
  loc1.`Location Code` AS location_code_1,
  loc1.Latitude AS lat1,
  loc1.Longitude AS lon1,
  loc2.`Location Code` AS location_code_2,
  loc2.Latitude AS lat2,
  loc2.Longitude AS lon2
FROM locations loc1
CROSS JOIN locations loc2;

如果你习惯老式的SQL写法，也可以用不带ON条件的隐式交叉连接，效果完全一样：

SELECT
  loc1.`Location Code` AS location_code_1,
  loc1.Latitude AS lat1,
  loc1.Longitude AS lon1,
  loc2.`Location Code` AS location_code_2,
  loc2.Latitude AS lat2,
  loc2.Longitude AS lon2
FROM locations loc1, locations loc2;

这两种写法都会生成所有地点的两两组合（含自身配对），正好满足你后续计算距离的需求。

内容的提问来源于stack exchange，提问作者Andrew Moseley