Mysql 子查询作为输入第二个查询

Question

我想计算与mysql记录的距离，首先我获取所有坐标并保存为LineString对象，但我有错误。 mysql出了什么问题？

WITH tmp AS
  (SELECT GROUP_CONCAT(CONCAT_WS(' ',lat,lng) SEPARATOR ', ')  FROM track WHERE vh_id='75' AND DATE(tdate)='2016-06-09' ORDER BY tdate)
SELECT ST_Length(ST_GeomFromText(tmp));

Answer 1

首先，MySQL不支持WITH子句；其次，您应该定义像10.4 User-Defined Variables这样的变量.

你可以将你的sql更改为此；)

SELECT GROUP_CONCAT(CONCAT_WS(' ',lat,lng) SEPARATOR ', ') INTO @tmp
FROM track
WHERE vh_id='75' AND DATE(tdate)='2016-06-09' ORDER BY tdate;
SELECT ST_Length(ST_GeomFromText(@tmp));

或者仅使用一个查询:

SELECT ST_Length(ST_GeomFromText(GROUP_CONCAT(CONCAT_WS(' ',lat,lng) SEPARATOR ', ')))
FROM track
WHERE vh_id='75' AND DATE(tdate)='2016-06-09' ORDER BY tdate;