SQLite不支持列相乘问题：含CASE分支的SQL计算需求咨询

嘿，我懂你遇到的问题了——你在SQLite里尝试用CASE语句生成的别名列和另一列相乘，但发现行不通对吧？这其实是SQL的执行顺序导致的：SELECT子句里定义的别名，在同一个SELECT里是没法直接引用的，SQLite也遵循这个规则。下面给你两种可行的解决方案：

方案1：重复CASE表达式 #

这是最直接的方法，把你的CASE逻辑直接写在相乘的表达式里，虽然有点重复，但SQLite能正确执行：

SELECT 
  -- 其他字段...
  CASE 
    WHEN facebook_post_audience >= 10000 THEN 15 
    WHEN facebook_post_audience >= 3000 AND facebook_post_audience <= 9999 THEN 10 
    WHEN facebook_post_audience >= 1000 AND facebook_post_audience <= 2999 THEN 7.5 
    WHEN facebook_post_audience >= 100 AND facebook_post_audience <= 999 THEN 5 
    ELSE 0 
  END AS 'Fb reward category',
  facebook_post_number AS 'No. of FB posts/shares',
  -- 直接重复CASE逻辑来计算乘积
  (CASE 
    WHEN facebook_post_audience >= 10000 THEN 15 
    WHEN facebook_post_audience >= 3000 AND facebook_post_audience <= 9999 THEN 10 
    WHEN facebook_post_audience >= 1000 AND facebook_post_audience <= 2999 THEN 7.5 
    WHEN facebook_post_audience >= 100 AND facebook_post_audience <= 999 THEN 5 
    ELSE 0 
  END) * facebook_post_number AS 'FB reward'
  -- 其他字段...
FROM your_table; -- 替换成你的实际表名

方案2：使用CTE或子查询（更整洁） #

如果你的CASE逻辑比较复杂，重复写会显得冗余，推荐用CTE（公共表表达式）或者子查询，先把CASE计算的结果提前算好，外层查询直接引用别名即可：

用CTE的示例： #

WITH post_rewards AS (
  SELECT 
    -- 其他字段...
    facebook_post_audience,
    facebook_post_number,
    CASE 
      WHEN facebook_post_audience >= 10000 THEN 15 
      WHEN facebook_post_audience >= 3000 AND facebook_post_audience <= 9999 THEN 10 
      WHEN facebook_post_audience >= 1000 AND facebook_post_audience <= 2999 THEN 7.5 
      WHEN facebook_post_audience >= 100 AND facebook_post_audience <= 999 THEN 5 
      ELSE 0 
    END AS 'Fb reward category'
  FROM your_table
)
SELECT 
  -- 其他字段...
  'Fb reward category',
  facebook_post_number AS 'No. of FB posts/shares',
  'Fb reward category' * facebook_post_number AS 'FB reward'
  -- 其他字段...
FROM post_rewards;

用子查询的示例： #

SELECT 
  -- 其他字段...
  'Fb reward category',
  facebook_post_number AS 'No. of FB posts/shares',
  'Fb reward category' * facebook_post_number AS 'FB reward'
  -- 其他字段...
FROM (
  SELECT 
    -- 其他字段...
    facebook_post_audience,
    facebook_post_number,
    CASE 
      WHEN facebook_post_audience >= 10000 THEN 15 
      WHEN facebook_post_audience >= 3000 AND facebook_post_audience <= 9999 THEN 10 
      WHEN facebook_post_audience >= 1000 AND facebook_post_audience <= 2999 THEN 7.5 
      WHEN facebook_post_audience >= 100 AND facebook_post_audience <= 999 THEN 5 
      ELSE 0 
    END AS 'Fb reward category'
  FROM your_table
) AS sub_query;

这两种方案都能完美解决你的列相乘需求，选哪种取决于你的CASE逻辑复杂度——简单逻辑用方案1快速，复杂逻辑用方案2更易维护。

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