代码之家  ›  专栏  ›  技术社区  ›  Brian Bruman

在SELECT Case语句中组合行

case join sql mysql
  •  0
  • Brian Bruman  · 技术社区  · 7 年前

    我有这个问题 

    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 回复  |  直到 7 年前
        1
  •  1
  •   Gordon Linoff    7 年前

    我认为这会产生你想要的结果: 

    SELECT q.question_id, q.question, q.answer_id, t.word,
       MAX(CASE WHEN a.id = q.answer_id THEN a.answer 
           END) AS answer, 
       MAX(CASE WHEN a.id <> q.answer_id THEN a.answer 
           END) AS incorrect_answer_1, 
       MIN(CASE WHEN a.id <> q.answer_id THEN a.answer 
           END) AS incorrect_answer_2 
FROM questions q JOIN
     answers a
     ON a.question_id = q.question_id JOIN
     nlp_terms t
     ON q.question_id = t.question_id
WHERE q.question_id = 1
GROUP BY q.question_id, q.question, q.answer_id, t.word;
        2
  •  0
  •   rob    7 年前

    您是否尝试过“WHERE questions.question\u id='1'且答案不为空”

    参考号: http://www.dofactory.com/sql/where-isnull

    推荐文章
    Badal Solanki  ·  如何在MySQL中对字符串进行算术运算?
    1 年前
    Saleh Mehdiyev  ·  我想重构我的Laravel代码以遵守SOLID原则
    1 年前
    hello_programmers  ·  Mysql从其他表输出一列
    1 年前
    Community wiki  ·  这个MySQL语句出了什么问题?
    1 年前
    Community wiki  ·  优化从同一表中提取的多列的查询
    1 年前
    IJustSentYouAPackage  ·  MySQL错误:引用的表中缺少约束的列[已关闭]
    1 年前
    Popo  ·  Sql查询:返回数据库中不可用的where条件
    1 年前
    Roberts  ·  DATE_FORMAT和STR_TO_DATE不工作
    1 年前
    Hamdan Nuramdani  ·  对账单中一周内不同表中的数据求和
    1 年前
    Kugelfisch  ·  用php为数据库加密数据
    1 年前
    关于  
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号