将指令与SQLite中的子查询结合

本文介绍了将指令与SQLite中的子查询结合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个看起来像这样的SQLite表:

I have a SQLite table that looks like this:

ID_TABLE POINTS_A_TABLE POINTS_B_TABLE id number id_a points_a id_b points_b -------------- ---------------- ---------------- smith 1 smith 11 smith 25 gordon 22 gordon 11 gordon NULL butch 3 butch 11 butch 26 sparrow 25 sparrow NULL sparrow 44 white 76 white 46 white NULL

使用以下命令

SELECT id, avg(points_a) FROM (SELECT id_a AS id, points_a FROM points_a_table UNION ALL SELECT id_b AS id, points_b FROM points_b_table) GROUP BY id ORDER BY avg(points_a) DESC;

我能够获得与每个名称相关的平均分数(此处有更多详细信息)

i'm able to get the average of points associated with each name (more details here)

id avg(points_a) white 46.0 [(46+0)/2] sparrow 44.0 [(0+44)/2] butch 18.5 [(11+26)/2] smith 18.0 [(11+25)/2] gordon 11.0 [(11+0)/2]

现在，我想将结果列 id 与 ID_TABLE 中的相应列 number 与ID_TABLE.number LESS匹配比26 .结果应为( number |平均值):

Now I'd like to match the resulting column id with the corresponding columnnumber in ID_TABLE with ID_TABLE.number LESS THAN 26. The result should be (number|average):

76 46.0 [(46 + 0)/2]

25 44.0 [(0+44)/2] 3 18.5 [(11+26)/2] 76 18.0 [(11+25)/2] 22 11.0 [(11+0)/2]

如何通过将新说明与以前的说明相结合，在一个查询中全部做到?

How can I do that all in one query, by combining new instructions with the previous ones ?

推荐答案

您需要执行JOIN，然后稍加修改分组以保持聚合函数正常工作.假定 points_a 或 points_b 中的每个对应记录在 id_table 中正好有一条记录:

You'll need to do a JOIN and then modify your grouping slightly to keep aggregate function working. Assuming that there is exactly one record in id_table for every corresponding record in points_a or points_b:

SELECT i.number, avg(pts.points) AS average_points FROM (SELECT id_a AS id, points_a AS points FROM points_a_table UNION ALL SELECT id_b AS id, points_b AS points FROM points_b_table) AS pts INNER JOIN id_table i ON i.id = pts.id GROUP BY pts.id, i.number WHERE i.number < 26 ORDER BY avg(pts.points) DESC;