我想做一张这样的桌子:

name  | Math | Physics | History | Biology | Gym
-------------------------------------------------------
Johnat|   5  |    3    |         |         |
Sarah |   1  |         |  2      |  3      | 2

我有一个这样的SQL结构:
艾因
id  |     nimetus   |
-----------------
1   |   Math     |
2   |   Physics  |
3   |   History  |
4   |   Biology  |
5   |   Gym      |

学生
id  |     name   |
-----------------
1   |   Johnat   |
2   |   Sarah    |

艾茵嫒学生
id  |     aine_id | student_id   |
---------------------------------
1   |   1         |   1          |
2   |   2         |   1          |
3   |   1         |   2          |
4   |   3         |   2          |
5   |   4         |   2          |
6   |   5         |   2          |

希恩
id  |     tulemus | aine_tudeng_id|
---------------------------------
1   |   5         |   1          |
2   |   3         |   2          |
3   |   1         |   3          |
4   |   2         |   4          |
5   |   3         |   5          |
6   |   2         |   6          |

现在我做了一个像这样的查询,它包含了我需要的所有数据
SELECT t.name,a.nimetus,h.tulemus FROM aine_student at
                                  INNER JOIN student t ON t.student_id=at.student_id
                                  INNER JOIN aine a ON a.aine_id=at.aine_id
                  INNER JOIN hinne h ON h.aine_student_id=at.aine_student_id

查询结果
  name  |   nimetus   | tulemus|
---------------------------------
Johnat  |    Math      |   5   |
Sarah   |    Math      |   1   |
Johnat  |    Physics   |   3   |
Sarah   |    History   |   2   |
Sarah   |    Biology   |   3   |
Sarah   |     Gym      |   2   |

但是我想这个查询不适合这个表,所以我为separateaine and name,tulemus
  function getNimetus()
  {
    $query = $this->db->prepare("SELECT distinct a.nimetus FROM aine_student at
                                  INNER JOIN student t ON t.student_id=at.student_id
                                  INNER JOIN aine a ON a.aine_id=at.aine_id
                  INNER JOIN hinne h ON h.aine_student_id=at.aine_student_id ");
      $query->execute();
      $exist = $query->fetchAll();


      if(!$exist) {
           throw new Exception('DB error');  //you can send your exception
        }

        $nimetus=[];

        for ($i=0; $i < count($exist); $i++) {
          $nimetus[]=$exist[$i]["nimetus"];
        }

        return $nimetus;
  }



  function getTulemus()
  {
    $query = $this->db->prepare("SELECT t.name,h.tulemus FROM aine_student at
                                  INNER JOIN student t ON t.student_id=at.student_id
                                  INNER JOIN aine a ON a.aine_id=at.aine_id
                  INNER JOIN hinne h ON h.aine_student_id=at.aine_student_id ");
      $query->execute();
      $exist = $query->fetchAll();


      if(!$exist) {
           throw new Exception('DB error');  //you can send your exception
        }

        $tulemus=[];

        for ($i=0; $i < count($exist); $i++) {
          $tulemus[]=["Nimi"=>[$exist[$i]["nimi"]=>$exist[$i]["tulemus"]]];
        }

        return $tulemus;
  }


function createTable() {
  $nimi=$this->getNames();
  $nimetus=$this->getNimetus();
  $tulemus=$this->getTulemus();
  echo "<table><thead><th>Nimi</th>";
  for ($i=0; $i <count($nimetus) ; $i++) {
    echo "<th>".$nimetus[$i]."</th>";
  }
  echo "</thead><tbody>";
  echo "<pre>".print_r($tulemus,true)."</pre>";
  for ($i=0; $i <count($tulemus) ; $i++) {
    foreach ($tulemus[$i]["Nimi"] as $key => $value) {
      if($key==)
    }
  }
}

但这就是我所走的路。我真的很难做这张桌子,所以任何帮助都是值得感谢的。

最佳答案

这个过程称为pivot。
这是通过组合MAX和CASE来完成的
查询

SELECT
   student.name
 , MAX(
     CASE
       WHEN Aine.nimetus = 'Math'
       THEN Hinne.tulemus
       ELSE ''
     END
   )
    AS Math
 , MAX(
     CASE
       WHEN Aine.nimetus = 'Physics'
       THEN Hinne.tulemus
       ELSE ''
     END
   )
    AS Physics
 , MAX(
     CASE
       WHEN Aine.nimetus = 'History'
       THEN Hinne.tulemus
       ELSE ''
     END
   )
    AS History
 , MAX(
     CASE
       WHEN Aine.nimetus = 'Biology'
       THEN Hinne.tulemus
       ELSE ''
     END
   )
    AS Biology
 , MAX(
     CASE
       WHEN Aine.nimetus = 'Gym'
       THEN Hinne.tulemus
       ELSE ''
     END
   )
    AS Gym

FROM
 student

INNER JOIN
 Aine_student
ON
 student.id =  Aine_student.student_id

INNER JOIN
 Aine
ON
  Aine.id = Aine_student.aine_id

INNER JOIN
 Hinne
ON
 Aine.id = Hinne.aine_student_id

GROUP BY
 student.name

结果
我的结果和你预期的不同。
我认为你在制作例外结果表时犯了一些错误。
|   name | Math | Physics | History | Biology | Gym |
|--------|------|---------|---------|---------|-----|
| Johnat |    5 |       3 |         |         |     |
|  Sarah |    5 |         |       1 |       2 |   3 |

演示http://www.sqlfiddle.com/#!9/62aa4a/1

关于php - 如何根据名称放置单元格值?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46608144/

10-12 07:33
查看更多