android - 带大小写的 SQL 请求按顺序抛出 "(1) 1st ORDER BY term does not match any column in the result set"

Question

SQL 游标有一个小问题。我正在尝试在 android 上执行 SQL 命令。我试图按案例对返回的内容进行排序，但系统似乎不接受返回值(？!)我什么都试过了!你有解决办法吗？ ;)

cursor = db.rawQuery(c, null);
String c = "SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%" + 
    TextUtils.join("%", arr) + "%' UNION ALL SELECT * FROM words WHERE
    UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%'
    ORDER BY CASE WHEN UPPER(descriptions) LIKE '" + s + "' THEN 1
    WHEN UPPER(descriptions) LIKE '" + s + "|%' THEN 2 WHEN
    UPPER(descriptions) LIKE '%|" + s + "|%' THEN 2 WHEN UPPER(descriptions)
    LIKE '%|" + s + "' THEN 2 WHEN UPPER(descriptions)
    LIKE '%" + TextUtils.join(" ", arr) + "%' THEN 3 ELSE 4 END, descriptions ASC";

我需要像这样对命令进行排序吗？

SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%' 
UNION ALL
SELECT * FROM words WHERE UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%'                                     
ORDER BY
    CASE
        WHEN UPPER(descriptions) LIKE '" + s + "' THEN 1          
        WHEN UPPER(descriptions) LIKE '" + s + "|%' THEN 2 
        WHEN UPPER(descriptions) LIKE '%|" + s + "|%' THEN 2 
        WHEN UPPER(descriptions) LIKE '%|" + s + "' THEN 2 
        WHEN UPPER(descriptions) LIKE '%" + TextUtils.join(" ", arr) + "%' THEN 3 
        ELSE 4 
    END,
descriptions ASC

谢谢;)

编辑:插入搜索词“a test”后的 SQL 命令

SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%A%TEST%'
UNION ALL
SELECT * FROM words WHERE UPPER(descriptions) LIKE '%A%TEST%'
ORDER BY
    CASE
        WHEN UPPER(descriptions) LIKE 'A TEST' THEN 1
        WHEN UPPER(descriptions) LIKE 'A TEST|%' THEN 2
        WHEN UPPER(descriptions) LIKE '%|A TEST|%' THEN 2
        WHEN UPPER(descriptions) LIKE '%|A TEST' THEN 2
        WHEN UPPER(descriptions) LIKE '%A TEST%' THEN 3
        ELSE 4
    END,
descriptions ASC

表架构:

characters (t TEXT,s TEXT,jy TEXT,descriptions TEXT)
words (t TEXT,s TEXT,jy TEXT,descriptions TEXT)

Answer 1

当您从单个表中选择记录时，您可以使用这些记录中的任何内容进行排序。

但是，当您使用 UNION 组合多个查询时，排序是对整个结果进行的，因此您必须使用结果中的某些列进行排序。在这种情况下，这意味着您必须将计算移至查询本身:

SELECT t, s, jy, descriptions, CASE ... END AS ordernr FROM ...
UNION ALL
SELECT t, s, jy, descriptions, CASE ... END AS ordernr FROM ...
ORDER BY ordernr,
         descriptions