如何将命名的内部联接查询转换为代码点火器语法

Question

>我有以下查询

$query = $this->db->query(
        'SELECT ii.json
        FROM inventory ii
        INNER JOIN
        (
            SELECT json, MAX(id) as MAX_ID
            FROM inventory
            GROUP BY business_unit
        ) group_json ON ii.id = group_json.MAX_ID
        INNER JOIN business_units 
        ON ii.business_unit = business_units.id'
    );
    return $query->result_array();

我们不得不迁移到 MSSQL 测试服务器，不幸的是语法不正确。为了避免将来出现这种情况，我想将其转换为CodeIgniter类型查询。

谁能告诉我这个查询在 CodeIgniter 中会是什么样子，我已经尝试了一些事情，但我还没有接近。

Answer 1

SELECT ii.json
FROM inventory ii
INNER JOIN
(
    SELECT json, MAX(id) as MAX_ID
    FROM inventory
    GROUP BY business_unit, json    --<-- "json" needs to be in GROUP BY
) group_json                           -- Since it is in SELECT but not
ON ii.id = group_json.MAX_ID           -- contained in any aggregate function
INNER JOIN business_units 
ON ii.business_unit = business_units.id

如果在SELECT中使用任何聚合函数（MAX、MIN、AVG、SUM、COUNT），则该选择中的任何聚合函数中不包含的任何其他列都必须进入查询的GROUP BY子句。