mysql unknown,MySQL Unknown列on子句

第一个查询正在运行：

SELECT video.seller from video;

第二个不是：

SELECT SQL_CALC_FOUND_ROWS *,

t.name as tname

FROM video, type as t

INNER JOIN user

ON video.seller = user.id

WHERE video.type2 = t.id

AND thumbnail_blobKey != ''

AND disabled = 0

AND is_reel = 0

AND price!= 0

AND video.type != 4

GROUP BY video.id

ORDER BY video.id DESC LIMIT 0, 10

Error : Unknown column ‘video.seller’ in ‘on clause’

解决方法:

这是一个有趣的例子.格式更好：

SELECT SQL_CALC_FOUND_ROWS *, t.name as tname

FROM video, type as t INNER JOIN

user

ON video.seller = user.id

WHERE video.type2 = t.id AND

thumbnail_blobKey != '' AND disabled = 0 AND is_reel = 0 AND price!= 0 AND video.type != 4

GROUP BY video.id

ORDER BY video.id DESC

LIMIT 0, 10

问题是视频列在from子句的其余部分中没有被理解,因为,.这就是逗号的工作原理.有趣的是,只需将其替换为逻辑等效的交叉连接即可修复：

SELECT SQL_CALC_FOUND_ROWS *, t.name as tname

FROM video cross join type as t INNER JOIN

user

ON video.seller = user.id

WHERE video.type2 = t.id AND

thumbnail_blobKey != '' AND disabled = 0 AND is_reel = 0 AND price!= 0 AND video.type != 4

GROUP BY video.id

ORDER BY video.id DESC

LIMIT 0, 10

这在documentation中解释,原因是优先的.逗号的优先级低于交叉连接.因此,表达式中的连接被评估为“视频连接(类型连接用户)”.由于首先解释第二个连接,因此视频中的列不可知.

但是,表上确实有一个连接,因此最好写成：

SELECT SQL_CALC_FOUND_ROWS *, t.name as tname

FROM video join

type t

on video.type2 = t.id INNER JOIN

user

ON video.seller = user.id

WHERE thumbnail_blobKey != '' AND disabled = 0 AND is_reel = 0 AND price!= 0 AND video.type != 4

GROUP BY video.id

ORDER BY video.id DESC

LIMIT 0, 10

标签：mysql,sql

来源： https://codeday.me/bug/20190625/1283760.html