我有一个sqlite联合查询:
SELECT term,abstract, termid, nterm FROM
(SELECT term, subterm, termid, nterm FROM t1 WHERE (nterm like '%bayern%')
UNION
SELECT term, subterm, termid, nterm FROM t2 WHERE (subterm like '%bayern%')
) ORDER BY nterm+0 ASC
现在,在结果集中,我得到了按nterm/subterm自然顺序匹配的每个单词。不过,我想添加一个排序,它首先在nterm中匹配,然后在subterm中匹配(每个排序都由natsort排序)。
这可能吗?我试图在括号中为查询命名(分别使用AS t1和AS t2),但它返回了一个错误。
这里有一种方法
Select term,subterm,termid,nterm From
(
SELECT 1 as termkey,term, subterm, termid, nterm FROM t1 WHERE (nterm like '%bayern%')
UNION
SELECT 2, term, subterm, termid, nterm FROM t2 WHERE (subterm like '%bayern%')
) innerquery
order by termkey,nterm
如果我明白你的意思,无论如何都很容易适应。
注意,当你选择*From(somequery)时,你必须在括号中对查询进行别名,否则解析器会阻塞它。
这是上面的一个更详细的版本,使发生的事情更清楚
Select innerquery.term,innerquery.subterm,innerquery.termid,innerquery.nterm From
(
SELECT 1 as termkey,term, subterm, termid, nterm FROM t1 WHERE (nterm like '%bayern%')
UNION
SELECT 2, term, subterm, termid, nterm FROM t2 WHERE (subterm like '%bayern%')
) innerquery
order by innerquery.termkey,innerquery.nterm