这段sql如何转成Laravel ORM来写?
程序员文章站
2022-05-08 19:29:14
...
SELECT
ea.id,
ea.name,
ea.capital_balance,
ea.total_balance,
ea.available_balance,
ea.cashable_balance,
ea.market_value as old_market_value,
mv.market_value,
ea.total_balance + mv.market_value as total_value,
ea.brokerage_rate,
ea.state,
ea.description,
ea.created_at
FROM
external_accounts ea,
(
SELECT
c.external_account_id,
SUM(c.amount) AS market_value
FROM
(
SELECT
a.external_account_id,
a.total_count * b.price AS amount
FROM
main_inventories a
JOIN stocks b ON a.stock_code = b.code
) AS c
GROUP BY
c.external_account_id
) AS mv
WHERE
ea.id = mv.external_account_id
主要是子Select那一段不知道怎么写,之所以非要一条sql写完的原因就是因为涉及到排序。
回复内容:
SELECT
ea.id,
ea.name,
ea.capital_balance,
ea.total_balance,
ea.available_balance,
ea.cashable_balance,
ea.market_value as old_market_value,
mv.market_value,
ea.total_balance + mv.market_value as total_value,
ea.brokerage_rate,
ea.state,
ea.description,
ea.created_at
FROM
external_accounts ea,
(
SELECT
c.external_account_id,
SUM(c.amount) AS market_value
FROM
(
SELECT
a.external_account_id,
a.total_count * b.price AS amount
FROM
main_inventories a
JOIN stocks b ON a.stock_code = b.code
) AS c
GROUP BY
c.external_account_id
) AS mv
WHERE
ea.id = mv.external_account_id
主要是子Select那一段不知道怎么写,之所以非要一条sql写完的原因就是因为涉及到排序。
将表关系都关联起来,在查询,查询里的算法操作可以交由PHP完成,你可以看下这个
Laravel的关联,可以使用分组,返回字段和聚合函数
http://www.kancloud.cn/baidu/...