SQLite多表查询需求：实现带站点过滤的列车数据查询

数据类与DAO定义 #

Kotlin数据类 #

data class SearchedTrainInfo(
    val trainName: String?=null,
    val trainNumber: Int?=null,
    val fromStationName: String?=null,
    val fromStationCode: String?=null,
    val fromSTA: Int?=null,
    val fromSTD: Int?=null,
    val fromKM: Float?=null,
    val fromHaltNo: Int?=null,
    val toStationName: String?=null,
    val toStationCode: String?=null,
    val toSTA: Int?=null,
    val toSTD: Int?=null,
    val toKM: Float?=null,
    val toHaltNo: Int?=null,
    val runDays: Int?=null
)

DAO抽象方法 #

@Query("""
    -- 下方为完整SQL语句
    SELECT
        t.trainName,
        t.trainNumber,
        from_sta.stationName AS fromStationName,
        from_sched.stationCode AS fromStationCode,
        from_sched.sta AS fromSTA,
        from_sched.std AS fromSTD,
        from_sched.km AS fromKM,
        from_sched.haltNo AS fromHaltNo,
        to_sta.stationName AS toStationName,
        to_sched.stationCode AS toStationCode,
        to_sched.sta AS toSTA,
        to_sched.std AS toSTD,
        to_sched.km AS toKM,
        to_sched.haltNo AS toHaltNo,
        t.runDays
    FROM RoomTrains t
    LEFT JOIN RoomSchedule from_sched ON t.trainNumber = from_sched.trainNumber
        AND (
            from_sched.stationCode = t.srCode
            OR (from_sched.sta = 1 AND from_sched.std != 1)
        )
    LEFT JOIN RoomStations from_sta ON from_sched.stationCode = from_sta.stationCode
    LEFT JOIN RoomSchedule to_sched ON t.trainNumber = to_sched.trainNumber
        AND (
            to_sched.stationCode = t.destCode
            OR (to_sched.sta != 1 AND to_sched.std = 1)
        )
    LEFT JOIN RoomStations to_sta ON to_sched.stationCode = to_sta.stationCode
    WHERE
        (:fromStationFilter IS NULL AND :toStationFilter IS NULL)
        OR (
            EXISTS (
                SELECT 1 FROM RoomSchedule s1
                WHERE s1.trainNumber = t.trainNumber
                    AND s1.stationCode = :fromStationFilter
                    AND s1.stop = 1
            )
            AND EXISTS (
                SELECT 1 FROM RoomSchedule s2
                WHERE s2.trainNumber = t.trainNumber
                    AND s2.stationCode = :toStationFilter
                    AND s2.stop = 1
            )
        )
    GROUP BY t.trainNumber
""")
abstract fun getFilteredAndSortedTrainsDAO(
    fromStationFilter: String?,
    toStationFilter: String?,
): List<SearchedTrainInfo>

查询规则说明 #

• 无过滤条件：当fromStationFilter为null时，返回所有列车的起止站点信息，起止站通过两种规则判定：
  • 规则1：匹配RoomTrains表的srCode（始发站编码）和destCode（终点站编码）
  • 规则2：匹配RoomSchedule表中sta=1且std!=1的站点为始发站，sta!=1且std=1的站点为终点站
• 有过滤条件：返回同时经过两个指定站点且停靠（RoomSchedule.stop=1）的列车，过滤条件匹配RoomSchedule.stationCode

示例效果 #

• 无过滤时：返回所有列车的完整起止站信息，例如列车名称为Pune - Kolhapur SCSMT Special的对应数据
• 传入fromStationFilter=JJR, toStationFilter=HTK：返回所有在JEJURI（JJR）和HATKANAGALE（HTK）间运行且停靠的列车数据

内容的提问来源于stack exchange，提问作者Pawandeep Singh