mysql join与where,mysql – 查询WHERE子句和JOIN

我在使用WHERE子句和JOIN时遇到问题.

SELECT * FROM `CallDetailRecord`

WHERE `StartTime` >=1357102799000

AND `StartTime` <=1357880399000

JOIN `CallEvent` ON `EventID` = `CallEventID`

Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘JOIN CallEvent ON EventID = CallEventID

LIMIT 0, 1000′ at line 1

Execution Time : 0 sec

Transfer Time : 0 sec

Total Time : 0.004 sec

---------------------------------------------------

我只是试图通过时间“starttime”限制数据集,但我得到查询错误,它指的是

加入.

有任何想法吗？如果需要,我很乐意提供更多信息.

解决方法:

这是您的原始查询

SELECT * FROM `CallDetailRecord`

WHERE `StartTime` >=1357102799000

AND `StartTime` <=1357880399000

JOIN `CallEvent` ON `EventID` = `CallEventID`

LIMIT 1000

理论上,您可以执行以下操作

>在StartTime上具有WHERE的子查询

>按StartTime添加ORDER

>在子查询中执行LIMIT

>指定LEFT JOIN

这是一个这样的查询

SELECT * FROM

(

SELECT * FROM `CallDetailRecord`

WHERE `StartTime` >=1357102799000

AND `StartTime` <=1357880399000

ORDER BY StartTime

LIMIT 0,1000

) A LEFT JOIN `CallEvent` B ON `EventID` = `CallEventID`;

如果查询很慢,则在StartTime上添加索引

ALTER TABLE CallDetailRecord ADD INDEX (StartTime);

试试看 ！！！

标签：mysql,join

来源： https://codeday.me/bug/20190806/1602450.html