Android SQLite 查詢語法錯誤：CASE WHEN 子句中別名使用誤區

錯誤 SQL 分析

SELECT CASE WHEN createduser = 112
    THEN (createdplayerpts AS you AND connectedplayerpts AS them)
    ELSE (connectedplayerpts AS you AND createdplayerpts AS them)
FROM game WHERE (turn = ?)

錯誤點：CASE ... THEN (col1 AS alias1 AND col2 AS alias2) —— 括號內不能直接放多個 AS 別名，且 AND 是邏輯運算子，非欄位分隔符。

正確寫法：分別投射欄位

SELECT
    CASE WHEN createduser = ? THEN createdplayerpts ELSE connectedplayerpts END AS you,
    CASE WHEN createduser = ? THEN connectedplayerpts ELSE createdplayerpts END AS them
FROM game
WHERE turn = ?

• 每個輸出欄位獨立一個 CASE 運算式。
• 參數化查詢（?）防止 SQL 注入，且效能更好。
• 別名 AS you、AS them 放在各自運算式結尾。

Android 代碼對應調整

String sql = "SELECT " +
    "CASE WHEN " + DBHelper.COLUMN_CRE_USER + " = ? THEN " + DBHelper.COLUMN_CRE_PTS + " ELSE " + DBHelper.COLUMN_CON_PTS + " END AS you, " +
    "CASE WHEN " + DBHelper.COLUMN_CRE_USER + " = ? THEN " + DBHelper.COLUMN_CON_PTS + " ELSE " + DBHelper.COLUMN_CRE_PTS + " END AS them " +
    "FROM " + DBHelper.TABLE_GAME + " WHERE turn = ?";

Cursor c = db.rawQuery(sql, new String[]{
    String.valueOf(userId),  // for first CASE
    String.valueOf(userId),  // for second CASE
    String.valueOf(turn)
});

除錯建議

1. 先在 DB 工具（如 DB Browser for SQLite）直接跑 SQL，確認語法正確。
2. 再對照代碼字串拼接，注意空格、逗號、括號配對。
3. 善用 rawQuery 參數綁定，避免手動拼接字串導致注入或格式錯誤。