我正在使用 sqlite 数据库。
这些表格是:
ID_TABLE POINTS_A_TABLE POINTS_B_TABLE
id number id_a points_a id_a points_a
-------------- ---------------- ----------------
smith 1 smith 11 ...
gordon 22 gordon 11
butch 3 butch 11
sparrow 25 sparrow
white 76 white 46
等等。在这些命令之后
select id,
points_a_table.points_a, points_b_table.points_a, points_c_table.points_a, points_d_table.points_a
from id_table
left join points_a_table on points_a_table.id_a = id_table.id
left join points_b_table on points_b_table.id_a = id_table.id
left join points_c_table on points_c_table.id_a = id_table.id
left join points_d_table on points_d_table.id_a = id_table.id
group by id
我得到了这个结果,在每一行上我都有 id 和与 id 关联的点。
现在我想得到一个按行的平均点,排序按平均降序排列。
我想要的是:
sparrow| 56 [(44+68)/2]
white | 41 ([46+67+11)/3]
smith | 33 [(11+25+65)/3]
butch | 24 [(11+26+11)/3]
gordon | 11 [11/1]
我怎样才能做到这一点?
谢谢。
如果将所有点表混合在一起,则可以简单地计算每组的平均值:
SELECT id,
avg(points_a)
FROM (SELECT id_a AS id, points_a FROM points_a_table
UNION ALL
SELECT id_a AS id, points_a FROM points_b_table
UNION ALL
SELECT id_a AS id, points_a FROM points_c_table
UNION ALL
SELECT id_a AS id, points_a FROM points_d_table)
GROUP BY id
ORDER BY avg(points_a) DESC;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)