r - R中的SQlite中的列名不明确错误

Question

我是 SQL 新手，想查询我的数据库加载到 R 中（使用 SQLite）

我想进行一些与此类似的查询（但要解决列名不明确的问题）：

        dbGetQuery(airline,'SELECT min (price) FROM routes 
        JOIN airports ON routes.departure = airports.airportID JOIN cities ON airports.city = cities.cityID AND cities.name = "Berlin"
        JOIN airports ON routes.arrival = airports.airportID AND cities.name = "London"')

在 SQLite 中，它使用以下语句运行：

         SELECT min(price) 
         FROM routes 
         WHERE routes.departure 
         IN

         (SELECT airportID
         FROM cities, airports
         WHERE
         airports.city = cities.cityID AND 
         cities.name = "Berlin" )
    
         AND routes.arrival IN 
         (SELECT airportID
         FROM cities, airports
         WHERE
         airports.city = cities.cityID AND 
         cities.name = "London" )
      

但是，我没有找到将其翻译为 R 的方法（我收到错误：输入不完整）。

也许有人有想法？

标签： rsqlite

Answer 1

以下语句有效：

dbGetQuery(airline, 'SELECT min(price) FROM routes
JOIN airports ON routes.departure = airports.airportID 
JOIN cities ON airports.city = cities.cityID AND cities.name = "Berlin"
JOIN airports as a1 ON routes.arrival = a1.airportID 
JOIN cities as c ON a1.city = c.cityID AND c.name = "London"')

通过使用城市实例的别名，对名称的第二个引用被调整为在 routes.arrival 上的连接。