mysql - 将子查询移至 where 或 from 子句，因为我不需要它的值

Question

现在我的 sql 查询返回一个有效的 id 和距离对数组。我想更改我的查询以仅返回 id。不确定如何重新排列查询中的子查询。

SELECT distinct t2.id, ( 5 * distance_given1 ) AS distance_calculated 
FROM table1 t1 
inner join table2 t2 on t1.id = t2.m_id
HAVING distance_calculated < distance_given2 
ORDER BY distance_calculated LIMIT 0 , 20

Answer 1

只需将表达式移动到 WHERE 子句中即可。

SELECT DISTINCT t2.id
FROM table1 t1
INNER JION table2 t2 ON t1.id = t2.m_id
WHERE (5 * distance_given1) < distance_given2
ORDER BY (5 * distance_given1)
LIMIT 0, 20

如果表达式比较复杂，不想重复，可以使用子查询:

SELECT id 
FROM (SELECT distinct t2.id, ( 5 * distance_given1 ) AS distance_calculated 
      FROM table1 t1 
      inner join table2 t2 on t1.id = t2.m_id
      HAVING distance_calculated < distance_given2
      ORDER BY distance_calculated
      LIMIT 0 , 20) AS x