mysql - 获取自定义MYSQL查询结果

Question

SELECT
  route_locations.id
FROM points JOIN route_locations ON route_locations.location_id = points.location_id
WHERE points.id in (199, 205) 
group by route_locations.route_id

运行此查询后，我得到以下两个值:

id
1487
1491

第一个用于 ID 为 199 的点，第二个用于 ID 为 205 的点。

现在，根据我的规则，如果第一个大于第二个，它将返回 'HT' 否则返回 'RT' 我该怎么做？如果不可能我怎样才能得到这样的结果？

point_id    route_location_id    
199           1487
205           1491

Answer 1

使用自连接分别获取两个结果，然后比较它们。

SELECT IF(l1.id > l2.id, 'HT', 'RT')
FROM points as p1
JOIN route_locations AS l1 ON p1.location_id = l1.location_id
CROSS JOIN points AS p2
JOIN route_locations AS l2 ON p2.location_id = l2.location_id
WHERE p1.id = 199
AND p2.id = 205