获取一些表/数组的mysql查询

Question

i have some table scores like this...

id A  B  score
1  10 4  100
2  10 2  320
3  10 1  100
4  20 4  20
5  20 3  100
6  20 2  120
7  20 1  110 
8  30 4  30
9  30 3  200

and i want some query or php method to make output like

column A =>    10    20     30
         4 =   100   20     30
         3 =   null   100    200
         2 =   320   120    null
         1 =   100   110    null
         ^
        ||
       (coumn b)

or

         4 ,100,20,30
         3 ,null,100,200
         2 ,320,120,null
         1 ,100,110,null

so i want get column score but based on coumn B, if column A don't have a row score of one column B, will give null.

i have tried full join, cross join, etc but i failed to get that's like it.

  SELECT * FROM TABLE CROSS JOIN
       (SELECT * FROM TABLE CROSS GROUP BY B)
  ORDER BY B DESC

thanks

Answer 1

Here is another way you can do it doing a nested loop (using mysqli_*):

echo '<table>';

$stmt = $connection->prepare("SELECT B FROM TABLE ORDER BY B DESC");
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($b);
while($stmt->fetch()){

    echo '<tr>
              <td>'.$b.'</td>';

    $stmt2 = $connection->prepare("SELECT A FROM TABLE GROUP BY A ORDER BY A");
    $stmt2->execute();
    $stmt2->store_result();
    $stmt2->bind_result($a);
    while($stmt2->fetch)){

        $stmt3 = $connection->prepare("SELECT score FROM TABLE WHERE A = ? AND B = ?");
        $stmt3->bind_param("ii", $a, $b);
        $stmt3->execute();
        $stmt3->store_result();
        if($stmt3->num_rows > 0){
            $stmt3->bind_result($score);
            $stmt3->fetch();
            echo '<td>'.$score.'</td>';
        } else {
            echo '<td>NULL</td>';
        }
        $stmt3->close();

    } /* END OF WHILE LOOP OF SECOND STATEMENT */
    $stmt2->close();

    echo '</tr>'; /* END OF ROW */

} /* END OF WHILE LOOP */
$stmt->close();

echo '</table>';

But I'm pretty sure someone would answer your question in a single query.