消除MySQL左联接查询中的重复列 [英] Eliminate duplicate columns in MySQL left join query

问题描述

我有两个表: 问题(id_question，name_question，text_question) 和选择(id_choice，text_choice，id_question)

I have two tables: question(id_question, name_question, text_question) and choice(id_choice, text_choice, id_question)

我在php代码中的查询是:

my query in php code is :

$query =    ' SELECT q.name_question, q.text_question, c.text_choice'.
                        ' FROM question as q '.
                        ' LEFT JOIN choice as c ' .
                        ' ON q.id_question = c.id_question ' ;

当我检查结果时:

foreach ($db->loadObjectList() as $obj){

    echo $obj->name_question." ".$obj->text_question." ".$obj->text_choice."</br>";

}

我得到的是:

Q1 Q1_text C1
Q1 Q1_text C2
Q1 Q1_text C3

Q2 Q2_text C4
Q2 Q2_text C5

我想要的是:

Q1 Q1_text C1
           C2
           C3

Q2 Q2_text C4
           C5

那么在SQL查询中有没有办法做到这一点?以及如何以我想要的方式在PHP中显示查询结果?

So is there a way to do it in the SQL query ? And how to show the query result in PHP the way I want ?

感谢您的帮助.

推荐答案

这可以通过两种方法解决:

This can be solved with two ways:

SQL小提琴: http://www.sqlfiddle.com/#！ 2/19d46/14

SQL Fiddle: http://www.sqlfiddle.com/#!2/19d46/14

  SELECT case
       when c.text_choice = (select min(text_choice) from choice c2 where c2.id = q.id) then name_question
       else ""
       end as name_question,

       case
       when c.text_choice = (select min(text_choice) from choice c2 where c2.id = q.id) then text_question
       else ""
       end as  text_question,
       c.text_choice 

FROM question as q LEFT JOIN choice as c  
ON q.id = c.id
order by q.id, c.text_choice;

结果:

Q1  Q1_text C1
C2
C3
Q2  Q2_text C4
C5
C6

也使用PHP:

初次查看时，可以很容易地通过PHP并通过name_question对结果进行排序.

Using PHP as well:

On initial viewing this can easily be accomplished via PHP and ordering your results by name_question.

$query =    ' SELECT q.name_question, q.text_question, c.text_choice'.
                        ' FROM question as q '.
                        ' LEFT JOIN choice as c ' .
                        ' ON q.id_question = c.id_question '  order by q.name_question;

显示结果时:

$current_nameQuestion = "";    
foreach ($db->loadObjectList() as $obj){

        if($current_nameQuestion==$obj->name_question)
        {
           echo "       ".$obj->text_choice."</br>";
        }
        else
        {
           echo $obj->name_question." ".$obj->text_question." ".$obj->text_choice."</br>";
           $current_nameQuestion = $obj->name_question;
        }       
    }

上面的PHP代码仅在尚未输出name_question时才打印2列值.

The above PHP code, will only print the 2 column values when the name_question has not already been outputted.

这篇关于消除MySQL左联接查询中的重复列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！