当我尝试利用我在这里创建的 SQLite 数据库时，Android 崩溃了

Question

当我尝试创建动态列时出现以下错误。我认为这是合法的，但显然 Android 和 SQLite 不喜欢这样。我确信我错过了一些简单的事情，但我就是想不起来。

错误消息是：没有这样的列：sundayResult：，编译时：

CREATE TABLE DAYSUSED(
                _id INTEGER PRIMARY KEY autoincrement, 
                sunday INTEGER NOT NULL,
                monday INTEGER NOT NULL, 
                tuesday INTEGER NOT NULL, 
                wednesday INTEGER NOT NULL, 
                thursday INTEGER NOT NULL, 
                friday INTEGER NOT NULL, 
                saturday INTEGER NOT NULL);
SELECT _id
           , volume_level
           , vibrate
           , CASE WHEN sunday = 1 THEN 'Sun' ELSE ' ' END AS sundayResult
           , CASE WHEN monday = 1 THEN 'Mon' ELSE ' ' END AS mondayResult
           , CASE WHEN tuesday = 1 THEN 'Tues' ELSE ' ' END AS tuesdayResult
           , CASE WHEN wednesday = 1 THEN 'Wed' ELSE ' ' END AS wednesdayResult
           , CASE WHEN thursday = 1 THEN 'Thur' ELSE ' ' END AS thursdayResult
           , CASE WHEN friday = 1 THEN 'Fri' ELSE ' ' END AS fridayResult
           , CASE WHEN saturday = 1 THEN 'Sat' ELSE ' ' END AS saturdayResult
           , sundayResult || ' ' || mondayResult || ' ' || tuesdayResult || ' ' || wednesdayResult || ' ' || thursdayResult || ' ' || fridayResult || ' ' || saturdayResult || ' ' AS days_of_week
 FROM DAYSUSED

如果我直接调用实际列，则最后一行将起作用，例如：

, sunday || ' ' ||

而不是我当前拥有的列：

, sundayResult || ' ' ||

我已经查看了所有内容，但没有知道还可以尝试什么，因为我真的很想更改光标的数据。

谢谢， 凯莉

Answer 1

sundayResult 只是一个别名，您不能在同一查询中使用它。你需要这样的东西。如果volume_level和vibrate来自不同的表，则将其与子查询“days”连接

SELECT _id
           , volume_level
           , vibrate
           , days.sundayResult || ' ' || days.mondayResult || ' ' || days.tuesdayResult || ' ' || days.wednesdayResult || ' ' || days.thursdayResult || ' ' || days.fridayResult || ' ' || days.saturdayResult || ' ' AS days_of_week
from
(          select
           CASE WHEN sunday = 1 THEN 'Sun' ELSE ' ' END AS sundayResult
           , CASE WHEN monday = 1 THEN 'Mon' ELSE ' ' END AS mondayResult
           , CASE WHEN tuesday = 1 THEN 'Tues' ELSE ' ' END AS tuesdayResult
           , CASE WHEN wednesday = 1 THEN 'Wed' ELSE ' ' END AS wednesdayResult
           , CASE WHEN thursday = 1 THEN 'Thur' ELSE ' ' END AS thursdayResult
           , CASE WHEN friday = 1 THEN 'Fri' ELSE ' ' END AS fridayResult
           , CASE WHEN saturday = 1 THEN 'Sat' ELSE ' ' END AS saturdayResult
           FROM DAYSUSED
) days