mysql - 基于行值的不同选择语句

Question

我有一个包含两种不同 GPS 设备类型的数据库。我需要创建一个根据 GPS 设备类型进行过滤的查询。

(gl200) 设备类型查询如下所示:

SELECT t1.deviceid, t1.devicetime, t1.latitude, t1.longitude
FROM positions t1
INNER JOIN
    (SELECT MAX(id) as LatestGpsPoint
    FROM positions
        WHERE deviceid IN
            (SELECT deviceid FROM device_group WHERE groupid = 7)
            AND valid = 1
            AND attributes LIKE '%FRI%'
            AND network != 'null'
            AND devicetime >= (SELECT deploymentdate 
                              FROM devices
                              WHERE id = deviceid
                              LIMIT 1
            )
            GROUP BY deviceid
    ) t2
ON t1.id = t2.LatestGpsPoint
ORDER BY t1.deviceid ASC
LIMIT 0,100

其他设备类型 (dmt) 查询如下所示:

SELECT t1.deviceid, t1.devicetime, t1.latitude, t1.longitude
FROM positions t1
INNER JOIN
    (SELECT MAX(id) as LatestGpsPoint
    FROM positions
        WHERE deviceid IN
            (SELECT deviceid FROM device_group WHERE groupid = 7)
            AND valid = 1
            AND devicetime >= (SELECT deploymentdate 
                              FROM devices
                              WHERE id = deviceid
                              LIMIT 1
            )
            GROUP BY deviceid
    ) t2
ON t1.id = t2.LatestGpsPoint
ORDER BY t1.deviceid ASC
LIMIT 0,100

我试图在其中放置一个 IF ELSE END 语句，以根据 t1.protocol = 'gl200' 或 'dmt' 更改搜索条件。

Answer 1

尝试联合:

(SELECT t1.deviceid, t1.devicetime, t1.latitude, t1.longitude
FROM positions t1
INNER JOIN
    (SELECT MAX(id) as LatestGpsPoint
    FROM positions
        WHERE deviceid IN
            (SELECT deviceid FROM device_group WHERE groupid = 7)
            AND valid = 1
            AND attributes LIKE '%FRI%'
            AND network != 'null'
            AND devicetime >= (SELECT deploymentdate 
                              FROM devices
                              WHERE id = deviceid
                              LIMIT 1
            )
            GROUP BY deviceid
    ) t2
ON t1.id = t2.LatestGpsPoint)
UNION
(SELECT t1.deviceid, t1.devicetime, t1.latitude, t1.longitude
FROM positions t1
INNER JOIN
    (SELECT MAX(id) as LatestGpsPoint
    FROM positions
        WHERE deviceid IN
            (SELECT deviceid FROM device_group WHERE groupid = 7)
            AND valid = 1
            AND devicetime >= (SELECT deploymentdate 
                              FROM devices
                              WHERE id = deviceid
                              LIMIT 1
            )
            GROUP BY deviceid
    ) t2
ON t1.id = t2.LatestGpsPoint)