如果数据存在于其他表中，则插入到表中

Question

我使用 sqlite 在我的 android 应用程序中记录数据。我有表 AP 和读数。我只想将数据插入到表 Readings 中，前提是它存在于表 AP 中。

我现在的输出是这样的：

[{"building_id":"test",

"readings":[{"name":"B","routers":{"b4:e9:b0:58:24:80":"@wifi.id","5a:e3:47:cd:34:87":"yeygygegehe","8c:e1:17:9e:21:ec":"UKM CENTER","54:78:1a:f3:24:80":"@wifi.id","54:78:1a:f3:24:82":"[e-mail]","50:c7:bf:6e:ed:35":"Teh Sosro","30:0c:23:c8:6d:ac":"","74:b5:7e:f8:23:64":"Intra Kentungan","50:06:04:7a:68:b0":"@wifi.id","40:ee:dd:7a:f1:68":"Mediapro Banteng","9c:ad:97:36:b4:57":"DESKTOP-R93MVG7 6315","10:c1:72:f3:1c:e4":"CHATUCHA","b4:e9:b0:58:24:82":"[e-mail]","50:64:2b:8b:c7:4c":"Logitech","50:06:04:7a:68:b2":"[e-mail]","b4:75:0e:dd:97:5e":"CLEON @AP LT2 UTARA"},

"values":{"b4:e9:b0:58:24:80":-47,"5a:e3:47:cd:34:87":-41,"8c:e1:17:9e:21:ec":-61,"54:78:1a:f3:24:80":-56,"54:78:1a:f3:24:82":-55,"50:c7:bf:6e:ed:35":-73,"30:0c:23:c8:6d:ac":-50,"74:b5:7e:f8:23:64":-69,"50:06:04:7a:68:b0":-35,"40:ee:dd:7a:f1:68":-79,"9c:ad:97:36:b4:57":-31,"10:c1:72:f3:1c:e4":-79,"b4:e9:b0:58:24:82":-47,"50:64:2b:8b:c7:4c":-54,"50:06:04:7a:68:b2":-35,"b4:75:0e:dd:97:5e":-78}},

"friendly_wifis":[{"BSSID":"9c:ad:97:36:b4:57","SSID":"DESKTOP-R93MVG7 6315"},{"BSSID":"50:06:04:7a:68:b2","SSID":"[e-mail]"},{"BSSID":"5a:e3:47:cd:34:87","SSID":"yeygygegehe"}]}]

我希望它是这样的：

[{"building_id":"test",

"readings":[{"name":"B","routers":{"5a:e3:47:cd:34:87":"yeygygegehe","54:78:1a:f3:24:82":"[e-mail]","9c:ad:97:36:b4:57":"DESKTOP-R93MVG7 6315"},

"values":{"5a:e3:47:cd:34:87":-41,"54:78:1a:f3:24:82":-55,"9c:ad:97:36:b4:57":-31}},

"friendly_wifis":[{"BSSID":"9c:ad:97:36:b4:57","SSID":"DESKTOP-R93MVG7 6315"},{"BSSID":"50:06:04:7a:68:b2","SSID":"[e-mail]"},{"BSSID":"5a:e3:47:cd:34:87","SSID":"yeygygegehe"}]}]

readings 是读数表，friendly_wifis 是 AP_TABLE。如您所见，我的应用程序扫描了所有现有项目。我希望它使用 mac_id 作为参数仅保存与 AP_TABLE 中的数据匹配的数据。

Answer 1

尝试这个   "CREATE TABLE readings " + "(building_id TEXT NOT NULL ,                  + position_id TEXT NOT NULL , x FLOAT NOT NULL, y FLOAT NOT NULL,                  +  ssid TEXT NOT NULL , mac_id TEXT NOT NULL , rssi INTEGER NOT NULL)";编辑  String Query = "SELECT * FROM " + AP_TABLE + " WHERE " + "mac_id ='" + id+"'";