基于前4个字符的mysql连接

eblbsuwk 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(243)

我正试图根据第一个表中字段的前4个字符联接第二个表，但我不断收到“unknown column”问题。“on子句”中的“first4”，我不确定原因：

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
FROM questions
LEFT JOIN answers ON answers.correct_answer = questions.first4
WHERE player_name = 'Alpha Squad'
ORDER BY id ASC

（我意识到这些表的结构效率低下，但不幸的是，我不得不按原样处理它们）
table：https://pastebin.com/gur5ufxa
mysql数据库：https://pastebin.com/flgwtqmy
提前谢谢！

mysql Join

来源：https://stackoverflow.com/questions/50608365/mysql-join-based-on-the-first-4-characters

2条答案

按热度按时间

dzhpxtsq1#

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
FROM questions
LEFT JOIN answers ON answers.correct_answer = SUBSTRING(question,1,4) 
WHERE player_name = 'Alpha Squad'
ORDER BY id ASC

问题是first4在您尝试连接时不存在。勾选此处以查看处理select语句的顺序。
sql查询的执行顺序

赞(0）回复(0）举报 2021-06-23

e5njpo682#

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
FROM questions
LEFT JOIN answers ON answers.correct_answer = questions.correct
WHERE player_name = 'Alpha Squad'
ORDER BY id ASC

或者下一个与匹配的前4个连接

SELECT questions.id as id, question, answer, correct, SUBSTRING(question,1,4) as first4
    FROM questions
    LEFT JOIN answers ON SUBSTRING(answers.correct_answer,1,4) = SUBSTRING(question,1,4)
    ORDER BY id ASC

赞(0）回复(0）举报 2021-06-23

我来回答

基于前4个字符的mysql连接

2条答案

相关问题

热门标签

最新问答