使用嵌套 SELECT 的 SQLite 查询 [英] SQLite Query using nested SELECT

问题描述

我正在尝试从数据库中选择 cpu_time，但时间必须符合其他一些条件 - 即 build_version、test_letter、design_index、multi_thread 和 test_index

I'm trying to select cpu_time from the db, but the time must correspond with a few other criteria - i.e. build_version, test_letter, design_index, multi_thread, and test_index

我认为可行的是(内部的 SELECT DISTINCT 语句自己工作):

What I thought would work was (the inner SELECT DISTINCT statement works on its own):

set query [db eval \
    {SELECT DISTINCT cpu_time WHERE cpu_time IN 
            (SELECT DISTINCT mgc_version, test_type_letter, design_index, 
                             test_index, cpu_time, multi_thread 
                    FROM TestExecutions WHERE test_type_letter
                    BETWEEN $testletter AND $testletter)}]

***注意 - 这给了我一个没有这样的列:cpu_time"错误

***Note - this is giving me a "no such column: cpu_time" error

我的第一个 SELECT 将从不同的返回中提取所有项目.然后，从每次返回，我只想为每种类型的 $testletter 使用 cpu_time.

where my first SELECT would pull all items from a distinct return. Then, from each return, I wanted to ONLY use the cpu_time, for each type of $testletter.

这是用于生成只有 cpu_time 的 CSV 文件.我的问题很明显吗?

This is for generating CSV files that only have the cpu_time. Is it obvious what I'm getting wrong?

谢谢！

推荐答案

您应该始终使用 WHERE xxx IN (SELECT xxx FROM ...)，而不是在内部选择中选择多个项目.您可以在外部选择中添加这些，例如:

You should always use WHERE xxx IN (SELECT xxx FROM ...), instead of selecting multiple items in the inner select. You can add those in the outer select though, for example:

SELECT DISTINCT 
    mgc_version, 
    test_type_letter, 
    design_index, 
    test_index, 
    cpu_time, 
    multi_thread 
FROM TestExecutions
WHERE cpu_time IN 
(
    SELECT DISTINCT cpu_time 
    FROM TestExecutions 
    WHERE test_type_letter BETWEEN $testletter AND $testletter
)

这篇关于使用嵌套 SELECT 的 SQLite 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！