SELECT TB.ID, Latitude, Longitude, 111151.29341326 * SQRT( POW( -6 - `Latitude` , 2 ) + POW( 106 - `Longitude` , 2 ) * COS( -6 * 0.017453292519943 ) * COS( `Latitude` * 0.017453292519943 ) ) AS Distance
FROM `tablebusiness` AS TB
JOIN `tablecity` AS TC ON TB.City = TC.City
JOIN `businessestag` AS BC ON BC.BusinessID = TB.ID
JOIN `businessesdistricts` AS BD ON BD.BusinessID = TB.ID
JOIN `tabledistrict` AS TD ON TD.ID = BD.District
WHERE (
`Title` LIKE '%restaurant%'
OR `Street` LIKE '%restaurant%'
OR TB.City LIKE '%restaurant%'
OR BC.Tag LIKE '%restaurant%'
OR TD.District LIKE '%restaurant%'
)
AND (
- 6.0917668133836 < `Latitude`
AND `Latitude` < - 5.9082331866164
AND 105.90823318662 < `Longitude`
AND `Longitude` < 106.09176681338
)
ORDER BY Distance
LIMIT 0, 100
这个 mysql 经历了
然后我也想根据建筑来寻找。
所以我做了,就像有人建议的那样
SELECT
TB.ID,
Latitude,
Longitude,
111151.29341326 * SQRT(POW(-6 - `TB.Latitude`, 2) + POW(106 - `TB.Longitude`, 2) * COS(-6 * 0.017453292519943) * COS(`TB.Latitude` * 0.017453292519943)) AS Distance
FROM
`tablebusiness` AS TB
JOIN `tablecity` AS TC
ON TB.City = TC.City
JOIN `businessestag` AS BC
ON BC.BusinessID = TB.ID
JOIN `businessesdistricts` AS BD
ON BD.BusinessID = TB.ID
JOIN `tabledistrict` AS TD
ON TD.ID = BD.District
LEFT JOIN `tablebusiness` TBuilding
ON TBuilding.ID = TB.Building
WHERE
(`Title` LIKE '%restaurant%' OR `Street` LIKE '%restaurant%' OR TB.City LIKE '%restaurant%'
OR BC.Tag LIKE '%restaurant%' OR TD.District LIKE '%restaurant%' OR TBuilding.Title LIKE '%restaurant%')
AND (-6.0917668133836 < `TB.Latitude` AND `TB.Latitude` < -5.9082331866164
AND 105.90823318662 < `TB.Longitude` AND `TB.Longitude` < 106.09176681338)
ORDER BY
Distance
LIMIT
0, 100
然后我收到一条消息,指出纬度字段不明确。
我该怎么办?
我做了明显的添加TB。 在纬度之前
explain SELECT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326 * SQRT(POW(-6 - `TB.Latitude`, 2) + POW(106 - `TB.Longitude`, 2) * COS(-6 * 0.017453292519943) * COS(`TB.Latitude` * 0.017453292519943)) AS Distance
FROM
`tablebusiness` AS TB
JOIN `tablecity` AS TC
ON TB.City = TC.City
JOIN `businessestag` AS BC
ON BC.BusinessID = TB.ID
JOIN `businessesdistricts` AS BD
ON BD.BusinessID = TB.ID
JOIN `tabledistrict` AS TD
ON TD.ID = BD.District
LEFT JOIN `tablebusiness` TBuilding
ON TBuilding.ID = TB.Building
WHERE
(`Title` LIKE '%restaurant%' OR `Street` LIKE '%restaurant%' OR TB.City LIKE '%restaurant%'
OR BC.Tag LIKE '%restaurant%' OR TD.District LIKE '%restaurant%' OR TBuilding.Title LIKE '%restaurant%')
AND (-6.0917668133836 < `TB.Latitude` AND `TB.Latitude` < -5.9082331866164
AND 105.90823318662 < `TB.Longitude` AND `TB.Longitude` < 106.09176681338)
ORDER BY
Distance
LIMIT
0, 100
现在错误是#1054 - 未知列"TB"。"字段列表"中的纬度
你需要在你指的是Latitude
前面加上TB
别名
SELECT
TB.ID,
TB.Latitude,
...
可能会发生这种情况,Longitude
也会模棱两可,所以我建议在它前面加上适当的别名。
更新:
我还建议您删除TB.Latitude
和TB.Latitude
周围的引号,因为它们会导致"未知列"错误。
将表的别名放在前面,例如 TB。纬度
由于在第二个查询中,您联接tablebusiness
两次,因此会产生歧义。
因此,为了消除歧义,您还必须包含要从中Latitude and Longitude
列的表的别名。
正确的查询是 -
SELECT TB.ID,
TB.Latitude,
TB.Longitude,
<everything else remains the same.>
正确答案是Li0LiQ建议的答案
所以我不选择这个作为答案。
只是为了记录,最后一种情况是,它有效。再。谢谢Li0LiQ。您的答案就是所选答案。
explain SELECT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326 * SQRT(POW(-6 - TB.Latitude, 2) + POW(106 - TB.Longitude, 2) * COS(-6 * 0.017453292519943) * COS(TB.Latitude * 0.017453292519943)) AS Distance
FROM
`tablebusiness` AS TB
JOIN `tablecity` AS TC
ON TB.City = TC.City
JOIN `businessestag` AS BC
ON BC.BusinessID = TB.ID
JOIN `businessesdistricts` AS BD
ON BD.BusinessID = TB.ID
JOIN `tabledistrict` AS TD
ON TD.ID = BD.District
LEFT JOIN `tablebusiness` TBuilding
ON TBuilding.ID = TB.Building
WHERE
(TB.Title LIKE '%restaurant%' OR TB.Street LIKE '%restaurant%' OR TB.City LIKE '%restaurant%'
OR BC.Tag LIKE '%restaurant%' OR TD.District LIKE '%restaurant%' OR TBuilding.Title LIKE '%restaurant%')
AND (-6.0917668133836 < TB.Latitude AND TB.Latitude < -5.9082331866164
AND 105.90823318662 < TB.Longitude AND TB.Longitude < 106.09176681338)
ORDER BY
Distance
LIMIT
0, 100