android - 将UNION ALL与另一张表一起使用时，保留一张表中的所有记录

我的两个表-UserLog和UserInspection这样的UserLog UNION ALL UserInspection。

我想保留所有来自UserLog的记录，但想要保留一些UserInspection值与OWNER值相同的column owner记录。

插图：

String OWNER = "0394850935803";

ArrayList<HashMap<String, String>> list =
         new ArrayList<HashMap<String,String>>();

HashMap<String, String> dataMap = null;

table_log = "SELECT Type_Log, Start AS SortDate, Location,
             Remark, Odometer, NULL AS Start_odo, NULL AS owner
             FROM UserLog WHERE deleted !=1 ";

table_insp = "select Type, Date, Location, Observation, NULL,
              Start_odo, owner from UserInspection where deleted !=1 ";

final_query = table_log + " UNION ALL " + table_insp + " ORDER BY SortDate";

cur = dbAdapter.rawQuery(final_query, null);

cur.moveToFirst();

for (int j = 0; j < cur.getCount(); j++)
{
    /*Keep all the UserLog records but keep only*/
    /*those UserInspection records whose owner column value*/
    /*from cursor matches the OWNER String value*/
    if((cur.isNull(cur.getColumnIndex("owner")))
     || OWNER.equals(cur.getString(cur.getColumnIndex("owner")))){

      /*Code to get other column values*/
      dataMap = new HashMap<String, String>();
      dataMap.put("Location", location);
      list.add(dataMap);
    }
}

但是即使ArrayList list表中有一些记录，在UserLog中我什么也没得到。

最佳答案

只需将附加条件放入WHERE子句中，则无需在代码中检查它：

...
table_insp = "select Type, Date, Location, Observation, NULL, "+
             "Start_odo, owner from UserInspection "+
             "WHERE deleted !=1 "+
               "AND owner = ?";

final_query = table_log + " UNION ALL " + table_insp + " ORDER BY SortDate";
String[] parameters = new String[] { OWNER };

cur = dbAdapter.rawQuery(final_query, parameters);
...

关于android - 将UNION ALL与另一张表一起使用时，保留一张表中的所有记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24492395/