c# - 如何获得两个城市之间的最低票价

我有下面的桌子

Source  Destination   Fare
A         B            10
B         C            5
B         D            1
D         C            1
A         D            1

现在我想写一个查询，给我两个城市之间的最低票价。
例如，如果我想去A->C，那么最低票价是2 A->D->C
这个问题的MS Sql查询是什么。

最佳答案

试试这个：

;WITH Paths AS (
   -- Anchor query: get first step
   SELECT CAST(CONCAT(Source, '->', Destination) AS VARCHAR(MAX)) AS Path,
          Destination, Fare,
          IIF(Destination = 'C', 1, 0) AS Terminate
   FROM mytable
   WHERE Source = 'A'

   UNION ALL

   -- Recursive part: get next step
   SELECT CAST(CONCAT(Path, '->', t.Destination) AS VARCHAR(MAX)) AS Path,
          t.Destination, Fare = t.Fare + p.Fare,
          IIF(t.Destination = 'C', 1, 0) AS Terminate
   FROM mytable AS t
   JOIN Paths AS p ON t.Source = p.Destination
   WHERE p.Destination <> 'C'
)
SELECT Path, Fare
FROM (
  SELECT Path, Fare,
         RANK() OVER (ORDER BY Fare) AS rnk
  FROM Paths
  WHERE Terminate = 1) AS t
WHERE t.rnk = 1

这是一种蛮力方法：它使用递归cte来获取所有可能的路径。然后使用RANK我们可以选择具有最低票价的。
Demo here

关于c# - 如何获得两个城市之间的最低票价，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37421301/