我的表有 4 列:
这是我的数据库。
ID SSID BSSID RSSI
1. jbhd ed:2d:5c -60
2. ABCD ab:cd:17 -68
3. ijkl cs:gb:d6 -75
4. vxfs dc:5g:f4 -72
5. cxzv fg:4d:ac -54
6. ABCD ab:cd:17 -68
7. ertd bv:we:12 -57
8. erbc gd:56:lt -83
....
518. ABCD ab:cd:17 -68
519. asfd ag:4g:32 -60
520. aasd gd:5g:56 -79
我正在尝试编写一个函数,该函数查询数据库以返回特定记录和 2 个具有最高ID
.
到目前为止我所做的是:
public Cursor get3records(String mac, int level){
SQLiteDatabase db = this.getReadableDatabase();
Cursor res = db.rawQuery("SELECT * from Scans_table st where ID >= ( select ID from Scans_table where BSSID =? AND RSSI =? ) order by st.ID asc limit 3 ", new String[] {mac, String.valueOf(level)});
return res;
}
mac
和level
是决定我想从查询中获取哪些记录的参数。
例如,查看提供的示例数据。我想有所有的记录ab:cd:17
在BSSID
列,并-68
在RSSI
列(记录2,6,518),和2下一个记录最高的ID
(记录3,4,7,8,519,520)。
问题是,这个函数返回这样的结果:
ID SSID BSSID RSSI
2. ABCD ab:cd:17 -68
3. ijkl cs:gb:d6 -75
4. vxfs dc:5g:f4 -72
而我正在寻找的结果应该是这样的:
ID SSID BSSID RSSI
2. ABCD ab:cd:17 -68
3. ijkl cs:gb:d6 -75
4. vxfs dc:5g:f4 -72
6. ABCD ab:cd:17 -68
7. ertd bv:we:12 -57
8. erbc gd:56:lt -83
518. ABCD ab:cd:17 -68
519. asfd ag:4g:32 -60
520. aasd gd:5g:56 -79
如果id
s 是连续的,没有间隙(如您的示例中所示),您可以执行以下操作:
select st.*
from scans_table st join
(select st2.id
from scans_table st2
where bssid = ? and rssi = ?
) ids
on st.id in (ids.id, ids.id + 1, ids.id + 2);
如果 id 不是连续的,这会变得更加棘手。最简单的方法可能是实现一个lag()
:
select *
from (select st.*,
(select st2.bssid
from scans_table st2
where st2.id < st.id
order by st2.id desc
limit 1
) as prev_bssid,
(select st2.rssi
from scans_table st2
where st2.id < st.id
order by st2.id desc
limit 1
) as prev_rssi,
(select st2.bssid
from scans_table st2
where st2.id < st.id
order by st2.id desc
limit 1, 1
) as prev2_bssid,
(select st2.rssi
from scans_table st2
where st2.id < st.id
order by st2.id desc
limit 1, 1
) as prev2_rssi
from scans_table st
) st
where (bssid = ? and rssi = ?) or
(prev_bssid = ? and prev_rssi = ?) or
(prev2_bssid = ? and prev2_rssi = ?);
当然,如果您使用的是最新版本的 SQLite,您可以使用窗口函数,这会更容易:
select *
from (select st.*,
lag(bssid) over (order by id) as prev_bssid,
lag(rssi) over (order by id) as prev_rssid,
lag(bssid, 2) over (order by id) as prev2_bssid,
lag(rssi, 2) over (order by id) as prev2_rssid
from scans_table st
) st
where (bssid = ? and rssi = ?) or
(prev_bssid = ? and prev_rssi = ?) or
(prev2_bssid = ? and prev2_rssi = ?);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句