使用先前在同一 SQL 语句中定义的列短名称 [英] Using column short-names defined previously in the same SQL statement

问题描述

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

I'm making some statistics from a table having values for expected and actual 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,
     ( (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

区别只是，我想通过使用之前定义的列名来缩短表达式 - Tested 和 OK

The difference is only, I wanted to shorten the expression by using column names defined previously - Tested and OK

有可能吗?

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

P.S.: If it is of any importance, the table is:

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

推荐答案

在大多数数据库中，别名不能用于与已定义的查询相同的级别"(或范围)(因为它可能不会可以在解析查询时解析别名).您可以做的是将查询包装在可以访问别名的外部查询中，如下所示:

In most databases an alias can't be used on the same "level" (or scope) of the query as it has been defined (as it might not be possible to resolve the alias at the time the query is parsed). What you could do is to wrap the query in an outer query which would have access to the alias like this:

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

这篇关于使用先前在同一 SQL 语句中定义的列短名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！