SQLite查询三张表

Question

我正在开发一个 Android 应用程序，它将在列表视图中显示来自 sqlite 数据库的一些信息。我需要一些帮助来解决我的查询。

数据库看起来像这样：

[monitors] 1 --- <has> --- * [results] 1 --- <has> --- * [steps]

Table monitors has columns: _id | warning_threshold | alarm_threshold | monitor_name
Table results  has columns: _id | monitor_id        | timestamp       | test_info
Table steps    has columns: _id | result_id         | correct         | response_time

我正在尝试进行一个将返回的查询：
1) 所有行&监视器表中的列。
2) 结果表中每个监视器的最新 test_info。
3) 计算步骤表中每个结果正确 = true 的数量。

返回的游标应如下所示：

_id | monitor_name | warning_threshold | alarm_threshold | test_info   | correct_count
1   | 'hugo'       | 1000              | 1500            | 'some info' | 7
2   | 'kurt'       | 800               | 1200            | 'info.....' | 5

我的查询：

SELECT * FROM
  (SELECT monitors._id AS _id,
  monitors.monitor_name AS monitor_name,
  monitors.warning_threshold AS warning_threshold,
  monitors.alarm_threshold AS alarm_threshold,
  results.test_info AS test_info
  FROM monitors
  LEFT JOIN results
  ON monitors._id = results.monitor_id
  ORDER BY results.timestamp ASC) AS inner
GROUP BY inner._id;

我几乎让它工作了。我能够从监视器和结果中获取信息，但我仍然需要获取正确的计数。任何有关解决此查询的帮助将不胜感激。

Answer 1

这是我的方法，结合使用左连接、子查询和相关子查询：

SELECT monitors._id AS _id,
 monitors.monitor_name AS monitor_name,
 monitors.warning_threshold AS warning_threshold,
 monitors.alarm_threshold AS alarm_threshold,
 LastResults.test_info AS test_info,
 COUNT(CorrectSteps._id) AS correct_count
FROM monitors
LEFT JOIN 
 (SELECT * FROM results as r1 where timestamp =
   (SELECT Max(r2.timestamp) FROM results AS r2 WHERE r1.monitor_id=r2.monitor_id)) LastResults
ON monitors._id = LastResults.monitor_id
LEFT JOIN 
 (SELECT * FROM steps WHERE correct = 'true') CorrectSteps
ON LastResults._id = CorrectSteps.result_id
GROUP BY monitors._id;

Answer 2

像这样的东西应该有效。我还没有能够测试它，但希望它至少能让你开始。请注意，此查询还没有接近优化。午饭的时候写的很快：）

SELECT m._id,
       m.monitor_name,
       m.warning_threshold,
       m.alarm_threshold,
       (SELECT r.test_info
          FROM results r
         WHERE r.monitor_id = m._id
         ORDER BY r.timestamp ASC
         LIMIT 1) as 'test_info',
       (SELECT COUNT(_id)
          FROM steps s
         WHERE s.result_id IN (SELECT _id FROM results WHERE monitor_id = m._id)
           AND s.correct = 'true') as 'correct_count'
  FROM monitor m