如何在Sqlite中优先考虑一个表格

Question

我有两个表格。在这些表格中，我的停止ID可能在其中一个或两个中。如果我的停止ID不存在于B中，它应该从A中选择。如果它不存在于A中，它应该从B中选择。如果在两个表中都存在stop ID，那么我想给B优先并从中检索行B.下面的查询总是返回B的值。你能帮我解决这个问题吗？

String selectQuery = "SELECT " 
    + stop_id + "," 
    + name + "," 
    + latitude + "," 
    + longitude + "," 

    +"1 as "+priority 
    +" FROM "+ A 

    +" UNION SELECT " 
    + stop_id + "," 
    + name + "," 
    + latitude + "," 
    + longitude + "," 
    +"2 as "+priority 
    +" FROM "+ B 
    +" WHERE " + stop_id + " =? " 
    +" ORDER BY "+priority 
    +" LIMIT 1 "; 


    Cursor cursor = db.rawQuery(selectQuery, new String[] {id}); 

Answer 1

在你UNION ALL你应该WHERE条款同时适用于SELECT秒;您当前的代码仅将条件应用于B一侧，而A一方返回所有行。尽管如此，在末尾LIMIT 1隐藏了这个问题。

字符串连接不会使它容易看到，但你应该通过id两次，并在查询中使用两个?占位符：

SELECT stop_id, name, latitude, longitude, 1 as priority 
FROM A 
WHERE stop_id = ? 
UNION ALL 
SELECT stop_id, name, latitude, longitude, 2 as priority 
FROM B 
WHERE stop_id = ? 
ORDER BY priority DESC 
LIMIT 1