如何在mysql中使用聚合函数？
Sha
我有2张桌子
- 学生们
- 付款方式
在学生表中，我有类似
student_id(Primary key),
student_name,
student_university,
student_counselor
在付款表中，我有如下字段
payment_id(Primary key),
student_id(Foreign key reference to students table),
payable,
paid,
balance
我正在努力达到：
- 在下面的表格中显示结果，即每个顾问的学生在不同的大学。
- 特定辅导员属于特定大学的学生总数，以及如上表所示，应付，已付和余额的数量。
有见识吗？
马克西姆·塞梅尼欣（Maksym Semenykhin）
为学生表创建语句：
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 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;
学生和付款数据插入
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);
这是我们拥有的：
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)
查询自身以选择所有顾问：
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;
结果：
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 |
+-----------+------------+----------------+---------+------+---------+
另外，您不必student在student表中的所有字段上使用前缀。很明显，name在student表的手段学生姓名等。
这是简单的php代码来打印表格。
这是数据库连接类，因此您可以跳过它：
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);
主要的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结果
<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>本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
编辑于
相关文章
TOP 榜单
- 1
UITableView的项目向下滚动后更改颜色，然后快速备份
- 2
Linux的官方Adobe Flash存储库是否已过时？
- 3
用日期数据透视表和日期顺序查询
- 4
应用发明者仅从列表中选择一个随机项一次
- 5
Mac OS X更新后的GRUB 2问题
- 6
验证REST API参数
- 7
Java Eclipse中的错误13，如何解决？
- 8
带有错误“ where”条件的查询如何返回结果？
- 9
ggplot：对齐多个分面图-所有大小不同的分面
- 10
尝试反复更改屏幕上按钮的位置 - kotlin android studio
- 11
如何从视图一次更新多行（ASP.NET - Core）
- 12
计算数据帧中每行的NA
- 13
蓝屏死机没有修复解决方案
- 14
在 Python 2.7 中。如何从文件中读取特定文本并分配给变量
- 15
离子动态工具栏背景色
- 16
VB.net将2条特定行导出到DataGridView
- 17
通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException
- 18
在Windows 7中无法删除文件（2）
- 19
python中的boto3文件上传
- 20
当我尝试下载 StanfordNLP en 模型时，出现错误
- 21
Node.js中未捕获的异常错误，发生调用
我来说两句