如何在mysql中使用聚合函数?

I have 2 tables

我有两个表

1. students
2. 学生
3. payments
4. 支付

In students table I have fields like

在学生表格中，我有类似的领域

student_id(Primary key),
student_name, 
student_university, 
student_counselor

In payments table I have fields like

在支付表中，我有类似的字段

payment_id(Primary key),
student_id(Foreign key reference to students table), 
payable,
paid, 
balance

I am trying to achive:

我在努力实现:

• Display the result in the form below i.e each counselor has their students in different university.
• 在下面的表单中显示结果。每个辅导员都有他们在不同大学的学生。
• The total number of students belongs to particular university from particular counselor and how much amount is payable, paid and balance as shown in the table above.
• 学生的总数是属于某一所大学的，来自某一所大学的辅导员。如上表所示，有多少是应付的、支付的和结余的。

Any insight?

了解吗?

1 个解决方案

#1

Create statement for student table:

为学生表创建语句:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `university` varchar(45) DEFAULT NULL,
  `counselor` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Create statement payment table:

创建声明付款表:

CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `payable` int(11) NOT NULL DEFAULT '0',
  `paid` int(11) NOT NULL DEFAULT '0',
  `balance` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK payment student_id student id_idx` (`student_id`),
  CONSTRAINT `FK payment student_id student id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Students and payments data insertion

学生和支付数据插入

INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 1', 'u 1', 'c 1');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 2', 'u 1', 'c 1');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 3', 'u 1', 'c 2');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 4', 'u 1', 'c 2');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 5', 'u 2', 'c 3');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 6', 'u 2', 'c 3');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 7', 'u 2', 'c 4');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 8', 'u 2', 'c 4');
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('1', 500, 300, 200);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('2', 500, 300, 200);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('3', 400, 400, 400);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('4', 400, 400, 400);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('5', 100, 200, 300);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('6', 51, 52, 53);

And here what we have:

这里我们有:

mysql> select * from student;
+----+-----------+------------+-----------+
| id | name      | university | counselor |
+----+-----------+------------+-----------+
|  1 | student 1 | u 1        | c 1       |
|  2 | student 2 | u 1        | c 1       |
|  3 | student 3 | u 1        | c 2       |
|  4 | student 4 | u 1        | c 2       |
|  5 | student 5 | u 2        | c 3       |
|  6 | student 6 | u 2        | c 3       |
|  7 | student 7 | u 2        | c 4       |
|  8 | student 8 | u 2        | c 4       |
+----+-----------+------------+-----------+
8 rows in set (0.02 sec)


mysql> select * from payment;
+----+------------+---------+------+---------+
| id | student_id | payable | paid | balance |
+----+------------+---------+------+---------+
|  1 |          1 |     500 |  300 |     200 |
|  2 |          2 |     500 |  300 |     200 |
|  3 |          3 |     400 |  400 |     400 |
|  4 |          4 |     400 |  400 |     400 |
|  5 |          5 |     100 |  200 |     300 |
|  6 |          6 |      51 |   52 |      53 |
+----+------------+---------+------+---------+
6 rows in set (0.00 sec)

query itself to select all counselor:

查询自己选择所有的顾问:

SELECT 
    counselor,
    university,
    COUNT(name) AS 'no of students',
    SUM(payment.payable) AS payable,
    SUM(payment.paid) AS paid,
    SUM(payment.balance) AS balance
FROM
    student
        LEFT JOIN
    payment ON payment.student_id = student.id
GROUP BY counselor;

result:

结果:

mysql>     SELECT
    ->         counselor,
    ->         university,
    ->         COUNT(name) AS 'no of students',
    ->         SUM(payment.payable) AS payable,
    ->         SUM(payment.paid) AS paid,
    ->         SUM(payment.balance) AS balance
    ->     FROM
    ->         student
    ->             LEFT JOIN
    ->         payment ON payment.student_id = student.id
    ->     GROUP BY counselor;
+-----------+------------+----------------+---------+------+---------+
| counselor | university | no of students | payable | paid | balance |
+-----------+------------+----------------+---------+------+---------+
| c 1       | u 1        |              2 |    1000 |  600 |     400 |
| c 2       | u 1        |              2 |     800 |  800 |     800 |
| c 3       | u 2        |              2 |     151 |  252 |     353 |
| c 4       | u 2        |              2 |    NULL | NULL |    NULL |
+-----------+------------+----------------+---------+------+---------+

Also you don't have to use student prefix on all fields in student table. It's obvious that name in student table means student name and so on.

你也不用在学生表格的所有字段中使用学生前缀。很明显，学生表中的名字意味着学生名等等。

And here is simply php code to print table.

这里是要打印的php代码。

This is db connect class so you can skip it:

这是db connect类，你可以跳过它:

define('DB_MAIN', 'localhost|someroot|some|db');

class my_db{

    private static $databases;
    private $connection;

    public function __construct($connDetails){
        if(!is_object(self::$databases[$connDetails])){
            list($host, $user, $pass, $dbname) = explode('|', $connDetails);
            $dsn = "mysql:host=$host;dbname=$dbname";
            self::$databases[$connDetails] = new PDO($dsn, $user, $pass);
        }
        $this->connection = self::$databases[$connDetails];
    }

    public function fetchAll($sql){
        $args = func_get_args();
        array_shift($args);
        $statement = $this->connection->prepare($sql);
        $statement->execute($args);
         return $statement->fetchAll(PDO::FETCH_OBJ);
    }
}


$db = new my_db(DB_MAIN);

main php code itself:

主要的php代码本身:

$universities = $db->fetchAll('SELECT distinct university FROM student');
$counselors = $db->fetchAll('SELECT distinct counselor FROM student');
$payments_ = $db->fetchAll(' SELECT
    counselor,
    university,
    COUNT(name) AS \'no of students\',
    SUM(payment.payable) AS payable,
    SUM(payment.paid) AS paid,
    SUM(payment.balance) AS balance
FROM
    student
        LEFT JOIN
    payment ON payment.student_id = student.id
GROUP BY counselor;');

$payments = [];

foreach ($payments_ as $payment)
    $payments[$payment->counselor][$payment->university] = $payment;
?>



<table border="1">
    <tr>
        <td rowspan="2" >counselor</td>
<?php
    foreach ( $universities as $key => $university){ ?>

        <td colspan="4" ><?=$university->university ?> </td>
    <?php } ?>
    </tr>
    <tr>
    <?php foreach ( $universities as $university){?>
        <td>no of students</td>
        <td>payable</td>
        <td>paid</td>
        <td>balance</td>
    <?php } ?>
    </tr>
    <tr>
    <?php foreach ( $counselors as $counselor){?>
    <?php foreach ( $universities as $key => $university){
        $payment = $payments[$counselor->counselor][$university->university];
    ?>
        <?php if(!$key){?>
        <td><?=$counselor->counselor?></td>
        <?php } ?>
        <td><?=(int)$payment->{'no of students'}?></td>
        <td><?=number_format($payment->payable,0,',','')?></td>
        <td><?=number_format($payment->paid,0,',','')?></td>
        <td><?=number_format($payment->balance,0,',','')?></td>
    <?php } ?>
    </tr>
    <?php } ?>
</table>

html result

html结果

<table border="1">
  <tr>
    <td rowspan="2">counselor</td>

    <td colspan="4">u 1 </td>

    <td colspan="4">u 2 </td>
  </tr>
  <tr>
    <td>no of students</td>
    <td>payable</td>
    <td>paid</td>
    <td>balance</td>
    <td>no of students</td>
    <td>payable</td>
    <td>paid</td>
    <td>balance</td>
  </tr>
  <tr>
    <td>c 1</td>
    <td>2</td>
    <td>1000</td>
    <td>600</td>
    <td>400</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>c 2</td>
    <td>2</td>
    <td>800</td>
    <td>800</td>
    <td>800</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>c 3</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>2</td>
    <td>151</td>
    <td>252</td>
    <td>353</td>
  </tr>
  <tr>
    <td>c 4</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>2</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
</table>