我有三个数据库表项目,其中包含关于项目的数据
表">项目":
project_id | 名称 |
---|---|
1000 | 项目1 |
20000 | 项目2 |
30000 | 项目3 |
40000 | 项目4 |
排序由"ORDER BY";是在查询中看不到任何内容。
SELECT column1, column 2…
FROM table_name
WHERE [condition]
GROUP BY column1, column2
ORDER BY column1, column2;
在子查询中获取Marvel收入,这样您就可以按它进行排序。
SELECT p.project_id, p.name, stid.settings
FROM project p
LEFT JOIN (
SELECT sid.project_id,
CONCAT('[', GROUP_CONCAT(
JSON_OBJECT(
'name', sas.name
,'revenue', sid.revenue
) SEPARATOR ',')
,']') AS settings,
MAX(CASE WHEN sas.name = 'MARVEL' THEN sid.revenue END) AS marvel_revenue
FROM revenues sid
JOIN company sas ON sas.fk_setting_id = sid.setting_id
GROUP BY sid.project_id
) stid ON stid.project_id = p.project_id
ORDER BY stid.marvel_revenue DESC
LIMIT 0,20