mysql - 想在求和 1 列时从 3 个表中获取数据

Question

table (user)
+----------------------+
|id | name | course_id |
|----------------------|
|01 | cena | 24        |
|02 | john | 42        |
+----------------------+

table (courses)
+---------------------------------+
|id | user_id | coursename | fee  |
|---------------------------------|
|24 | 01      | Illustrator| 4000 |
|42 | 02      | Photoshop  | 3000 |
+---------------------------------+

table (fee)
+---------------------------+
|id | user_id | paid_amount |
|---------------------------|
|01 | 01      | 400         |
|02 | 02      | 800         |
|03 | 01      | 600         |
|04 | 02      | 1200        |
+---------------------------+

我试过这个查询但没有成功

$query = "SELECT A.id, A.name, C.coursename, C.fee, B.'total paid fee' FROM 'user' A

LEFT JOIN (SELECT user_id, SUM(paid_amount) 'total paid fee' FROM fee GROUP BY user_id) B ON A.id=B.user_id

LEFT JOIN 类(class) C ON A.id=C.user_id;";

<?php 
    $query = "above query";
    $query = $connect->prepare($query);
    $query->execute();
    if($query->rowCount()):
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
    echo $row['id']; 
    echo $row['name']; 
    echo $row['coursename']; 
    echo $row['fee']; 
    echo $row['total paid fee']; 
    } 
    endif; 
?>

Answer 1

删除表名周围的单引号，例如 'user'，您可以使用 bacticks ，此外不要在别名中使用空格，例如 'total paid fee'相反，您可以将其写为totalPaidFee或total_paid_fee`

SELECT A.id, 
       A.name, 
       C.coursename, 
       C.fee, 
       B.total_paid_fee, 
       C.fee - B.total_paid_fee AS remaining_fee 
FROM   `user` A 
       LEFT JOIN (SELECT user_id, 
                         Sum(paid_amount) total_paid_fee 
                  FROM   fee 
                  GROUP  BY user_id) B 
              ON A.id = B.user_id 
       LEFT JOIN courses C 
              ON A.id = C.user_id 

Demo