将指令与SQLite中的子查询结合 [英] Combine instructions with a subquery in 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;

这篇关于将指令与SQLite中的子查询结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！