我有这个问题
SELECT questions.question_id,
questions.question,
questions.answer_id,
nlp_terms.word,
( CASE
WHEN answers.id = questions.answer_id THEN answers.answer
END ) AS answer,
( CASE
WHEN answers.id != questions.answer_id THEN answers.answer
END ) AS incorrect_answer_1,
( CASE
WHEN answers.id != questions.answer_id THEN answers.answer
END ) AS incorrect_answer_2
FROM questions
JOIN answers
ON answers.question_id = questions.question_id
JOIN nlp_terms
ON questions.question_id = nlp_terms.question_id
WHERE questions.question_id = '1'
此查询输出:
╔═════════════╦═══════════════════════════════════════════╦═══════════╦═══════════╦════════╦════════════════════╦════════════════════╗
║ question_id ║ question ║ answer_id ║ word ║ answer ║ incorrect_answer_1 ║ incorrect_answer_2 ║
╠═════════════╬═══════════════════════════════════════════╬═══════════╬═══════════╬════════╬════════════════════╬════════════════════╣
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ What ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ continent ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ is ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ the ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ country ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ Lesotho ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ in? ║ NULL ║ South America ║ South America ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ What ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ continent ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ is ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ the ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ country ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ Lesotho ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ in? ║ Africa ║ NULL ║ NULL ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ What ║ NULL ║ Australia ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ continent ║ NULL ║ Australia ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ is ║ NULL ║ Australia ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ the ║ NULL ║ Australia ║ AustraliA ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ country ║ NULL ║ Australia ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ Lesotho ║ NULL ║ Australia ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ in? ║ NULL ║ Australia ║ Australia ║
╚═════════════╩═══════════════════════════════════════════╩═══════════╩═══════════╩════════╩════════════════════╩════════════════════╝
正如您所看到的,它带来了我想要的所有数据,只是我的case语句采用了自己的一行,而不是分组。
我有很多 GROUP BY
操作,但无法使其与预期输出相同:
╔═════════════╦═══════════════════════════════════════════╦═══════════╦═══════════╦════════╦════════════════════╦════════════════════╗
║ question_id ║ question ║ answer_id ║ word ║ answer ║ incorrect_answer_1 ║ incorrect_answer_2 ║
╠═════════════╬═══════════════════════════════════════════╬═══════════╬═══════════╬════════╬════════════════════╬════════════════════╣
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ What ║ Africa ║ South America ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ continent ║ Africa ║ South America ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ is ║ Africa ║ South America ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ the ║ Africa ║ South America ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ country ║ Africa ║ South America ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ Lesotho ║ Africa ║ South America ║ Australia ║
║ 1369 ║ What continent is the country Lesotho in? ║ 4106 ║ in? ║ Africa ║ South America ║ Australia ║
╚═════════════╩═══════════════════════════════════════════╩═══════════╩═══════════╩════════╩════════════════════╩════════════════════╝
我缺少什么来获得预期的输出?
2条答案
按热度按时间hfsqlsce1#
你试过“where questions.question\u id='1'and answer is not null”吗
参考文献:http://www.dofactory.com/sql/where-isnull
trnvg8h32#
我认为这会产生你想要的结果: