如何在FROM子句中使用ActiveRecord进行带有子查询的SQL查询。

huangapple go评论65阅读模式
英文:

How to use ActiveRecord for a sql query with subquery in FROM clause

问题

你知道如何翻译带有FROM子查询的SQL查询吗?

以下是一个示例:

SELECT 
    *
FROM
    cars
WHERE
    cars.id IN (
    SELECT
        cars.user_id
    FROM 
        cars AS cf, (SELECT
        cars.user_id, MAX(consumption) AS consumption FROM
        cars
    GROUP BY
        user_id) AS t1
    WHERE
        cars.consumption = t1.consumption
        AND
        cars.user_id = 2
        AND
        t1.user_id = cars.user_id)
英文:

Do you know how to translate a SQL query with a subquery in a FROM clause ?

Here is an example :

SELECT 
    *
FROM
    cars
WHERE
    cars.id IN (
    SELECT
        cars.user_id
    FROM 
        cars AS cf, (SELECT
        cars.user_id, MAX(consumption) AS consumption FROM
        cars
    GROUP BY
        user_id) AS t1
    WHERE
        cars.consumption = t1.consumption
        AND
        cars.user_id = 2
        AND
        t1.user_id = cars.user_id)

答案1

得分: 1

Car.where('id IN (SELECT cars.user_id FROM cars AS cf, (SELECT cars.user_id, MAX(consumption) AS consumption FROM cars GROUP BY user_id) AS t1 WHERE cars.consumption = t1.consumption AND cars.user_id = :user_id AND t1.user_id = cars.user_id)', user_id: 2)
英文:
Car.where('id IN (SELECT cars.user_id FROM cars AS cf, (SELECT cars.user_id, MAX(consumption) AS consumption FROM cars GROUP BY user_id) AS t1 WHERE cars.consumption = t1.consumption AND cars.user_id = :user_id AND t1.user_id = cars.user_id)', user_id: 2)

huangapple
  • 本文由 发表于 2020年1月6日 19:43:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/59611521.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定