代码之家 › 专栏 › 技术社区 › Raymart Calinao

水平动态联接表

mysql php

Raymart Calinao · 技术社区 · 7 年前

我需要一些帮助把桌子水平地连接起来我的桌子是

+---------+---------+
| Candidates Table  |
+---------+---------+
| can_id   | Name   | 
+---------+---------+
| 1       | Liza    |
| 2       | Sarah   |
| 3       | Jane    |
|         |         |
+---------+---------+

+---------+---------+
| Judges Table      |
+---------+---------+
| id      | Name    | 
+---------+---------+
| 1       | judge1  |
| 2       | judge2  |
| 3       | judge3  |
+-------------------+

+---------+---------------+--------+-------+
|                Score Table               |
+---------+-------+------------------------|
| sco_id  |  can_id| jud_id |crit_id |score|
+---------+--------+-----------------------+
| 1       |   1    |   2    |    1   |  87 |
| 2       |   1    |   3    |    1   |  89 |
| 3       |   1    |   1    |    1   |  80 |
+------------------------------------------+

我需要一个像这样的输出…

+---------+---------------+-------------+
|                Score board            |
+---------+---------+-------------------|
| Name    |  judge1 | judge2  | judge3  |
+---------+---------+-------------------|
| Liza    |   80    |    87   |    89   |  
|some data|some data|some data|some data|  
|some data|some data|some data|some data|  
+---------------------------------------+

注:crit_id是条件表中的条件id。

通常我会使用一些连接和子查询,但我的问题是我需要动态输出,如果我添加一个新的判断,它将自动生成一个新列。我需要至少一个包含所有评委分数的候选数据,然后在php上用参数循环得到其他候选数据,比如

php loop start
   <td>name</td> 
   <td>judge1 score</td>
   <td>judge2 score</td> 
php end loop

或者,如果我能让整个候选人名单上的评委打分更高,这样我就不会给每个候选人打分了

我试着研究类似的问题,比如

Concatenate more than two tables horizontally in SQL Server

我试着给自己编代码,但是我被困在了评委的行列中。

SELECT s.sco_id,c.Name,c.Municipalities 
FROM `tbl_scoring` s 
LEFT JOIN tbl_candidates c ON c.`can_id` = s.`can_id` 
WHERE s.can_id = 11 
  AND crit_id = 1 
ORDER BY s.jud_id asc

我需要一个动态生成的查询,它取决于评委的数量,要么用评委的分数获取候选数据,然后在php上循环,要么如果我不循环地获取所有数据,就更好了

0 回复 | 直到 7 年前

HTMHell 7 年前

初始化以下数组:

$judges = [];
$scores = [];
$candidates = [];

然后执行查询并循环结果。为每次迭代设置这些值:

$judges[$row['jud_id']] = 1;
$candidates[$row['can_id']] = $row['Name'];
$scores[$row['can_id']][$row['jud_id']] = $row['score'];

现在您要获取参与者判断名称,那么让我们运行一个sql查询:

$sql = 'SELECT Name FROM judges WHERE id IN (' . implode(',', array_keys($judges)) . ')';

在每次迭代中 $judges 数组:

$judges[$row['id']] = $row['Name'];

那么对于输出:

echo '<tr>';
echo '<td>Name</td>';
ksort($judges);
foreach ($judges as $name) {
    echo '<td>Judge: ' . $name . '</td>';
}
echo '</tr>';

foreach ($scores as $candidateId => $data) {
    echo '<tr>';
    echo "<td>$candidates[$candidateId]</td>";
    ksort($data);
    foreach ($data as $score) {
        echo "<td>$score</td>";
    }
    echo '</tr>;
}

我用过 ksort 在 $评委 和 $data 所以分数适合每个评委。

Djellal Mohamed Aniss 7 年前

首先,我们检索评分表上的评委id和姓名。

$judges = [];
$query = "SELECT id, name FROM Judges WHERE id IN ( SELECT DISTINCT jud_id FROM Score )";
// execute the query and store the results in the $judges array.

我们检索分数表中存在的候选人的ID和姓名。

$candidates = [];
$query = "SELECT id FROM Candidate WHERE id IN ( SELECT DISTINCT can_id FROM Score )";
// execute the query and store the results in the $candidates array.

然后,我们加入候选人和得分表。

$candidate_score = [];
$query = "SELECT Candidate.name, Candidate.id as candidate_id , Score.jud_id, Score.score FROM Candidate JOIN Score ON Score.can_id = Candidate.id";
// execute the query and store it in the $candidate_score array.

现在,对于每个候选人,我们将其分数填入$score_board数组中。

$score_board = [];

foreach ( $candidates as $candidat )
{
   $score_board[$candidat] = [];

   foreach ( $judges as $judge )
   {
      $judge_name = $judge['name'];
      $judge_id = $judge['id'];
      $score_board[$candidat][$judge_name] = get_judge_score($candidate_score,$candidat,$judge_id);
   } 
}

这就是为什么 get_judge_score 将工作:

function get_judge_score ( $scores , $candidate , $judge )
{
   $score_filtred = array_filter($scores, function ($score) use ($candidate,$judge) {
      return $score['jud_id'] == $judge && $score['candidate_id'] = $candidate;
   });

   return count($score_filtred) > 0 ? $score_filtred[0]['score'] : 0;
}