sql - 使用先前在同一 SQL 语句中定义的列短名称

Question

我正在从包含预期结果和实际结果值的表格中进行一些统计。

这个语句非常有效:

select model_ref AS [Model],
     count(id) AS Tested,
     count(case when expected_result = predicted_result then predicted_result end) AS OK,
     count(case when NOT(expected_result = predicted_result) then expected_result end) AS Wrong,
     ( (count(case when expected_result = predicted_result then predicted_result end)*1.0) / count(id) * 100  ) AS [Accuracy (%)]
from Results

但这不是:

select model_ref AS [Model],
     count(id) AS Tested,
     count(case when expected_result = predicted_result then predicted_result end) AS OK,
     count(case when NOT(expected_result = predicted_result) then expected_result end) AS Wrong,
     ( OK *1.0) / Tested * 100  ) AS [Accuracy (%)]
from Results

区别只是，我想通过使用之前定义的列名称来缩短表达式 - 已测试和确定

可能吗？

附注:如果它有任何重要性的话，表格是:

CREATE TABLE Results(
id INTEGER PRIMARY KEY AUTOINCREMENT,
expected_result TEXT,
image_id TEXT,
model_ref TEXT,
predicted_result TEXT,
test_date TEXT)

Answer 1

在大多数数据库中，别名不能在定义的查询的同一“级别”(或范围)上使用(因为在解析查询时可能无法解析别名) 。您可以做的是将查询包装在外部查询中，该查询可以访问别名，如下所示:

select 
    Model,
    Tested,
    OK,
    Wrong,
    (( OK *1.0) / Tested * 100  ) AS [Accuracy (%)] 
from (
    select model_ref AS [Model],
       count(id) AS Tested,
       count(case when expected_result = predicted_result then predicted_result end) AS OK,
       count(case when NOT(expected_result = predicted_result) then expected_result end) AS Wrong
    from Results
    group by model_ref
) a