SQLite中生成字段条件比较失效，为何rank始终为-1？

Ah, this is a classic SQL execution order gotcha—let me break down what's happening here.

The Core Problem #

You're trying to reference the aliases color_red and animal_tiger in the same SELECT clause where you define them, but SQL doesn't work that way.

SQL evaluates clauses in a specific logical order, and while the SELECT clause runs after the FROM clause, all expressions within SELECT are evaluated in parallel—not top-to-bottom like a procedural language. That means when the database calculates the rank column's CASE statement, the color_red and animal_tiger aliases don't exist yet. The database treats them as missing column names from the farm table, so those conditions (animal_tiger = 1 and color_red = 1) evaluate to false, pushing every row into the ELSE -1 branch.

Fixes to Get the Correct Rank #

You have a couple straightforward ways to fix this:

1. Repeat the CASE Expressions (Quick and Simple)

Instead of referencing the aliases, reuse the original CASE logic directly in the rank calculation:

CREATE VIEW animal_colors as 
select "farm"."color" as "color",
       CASE WHEN "farm"."color" = 'Red' THEN 1 ELSE 0 END AS "color_red",
       CASE WHEN "farm"."animal" = 'Tiger' THEN 1 ELSE 0 END AS "animal_tiger",
       CASE WHEN "farm"."animal" = 'Tiger' THEN 1 
            WHEN "farm"."color" = 'Red' THEN 0 
            ELSE -1 END AS "rank"
FROM "farm"

2. Use a Subquery or CTE (Cleaner for Complex Logic)

For better readability (especially if your flag logic grows more complex), calculate color_red and animal_tiger first in a subquery or CTE, then reference those values in the outer query:

CTE Version:

CREATE VIEW animal_colors as 
WITH farm_flags AS (
    SELECT "color",
           CASE WHEN "color" = 'Red' THEN 1 ELSE 0 END AS "color_red",
           CASE WHEN "animal" = 'Tiger' THEN 1 ELSE 0 END AS "animal_tiger"
    FROM "farm"
)
SELECT "color",
       "color_red",
       "animal_tiger",
       CASE WHEN "animal_tiger" = 1 THEN 1 
            WHEN "color_red" = 1 THEN 0 
            ELSE -1 END AS "rank"
FROM farm_flags

Subquery Version:

CREATE VIEW animal_colors as 
SELECT "color",
       "color_red",
       "animal_tiger",
       CASE WHEN "animal_tiger" = 1 THEN 1 
            WHEN "color_red" = 1 THEN 0 
            ELSE -1 END AS "rank"
FROM (
    SELECT "farm"."color" as "color",
           CASE WHEN "farm"."color" = 'Red' THEN 1 ELSE 0 END AS "color_red",
           CASE WHEN "farm"."animal" = 'Tiger' THEN 1 ELSE 0 END AS "animal_tiger"
    FROM "farm"
) AS farm_subquery

Either approach will correctly calculate the rank based on your rules: 1 for Tigers, 0 for other red animals, -1 for everything else.

内容的提问来源于stack exchange，提问作者Ian McInerney