php - 优化高级 SQL 查询
问题描述
学生表:存储学生的基本信息
fee_received表:存储每个学生在一年中每个月支付的每月学费信息。
问题:
我需要在 2018 年的最后 7 个月中的任何一个月中获取所有费用违约者。最重要的是,我需要每个学生的所有这些月(例如:3、4、6),以便我可以将其展示给管理员哪个学生在哪个月份拖欠费用。
表架构
CREATE TABLE `fee_received` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`challan_no` int(11) NOT NULL DEFAULT '0',
`student_id` int(11) NOT NULL,
`class_id` int(2) NOT NULL,
`amount` int(5) NOT NULL,
`arrears` int(6) NOT NULL DEFAULT '0' COMMENT 'arrears in monthly tuition fee.',
`fine` int(11) NOT NULL DEFAULT '0' COMMENT 'total fine in current challan',
`fee_month` int(2) NOT NULL,
`fee_year` int(4) NOT NULL,
`dt` date NOT NULL,
`multi_fee` tinyint(11) NOT NULL DEFAULT '0',
`submitted_with_admission` tinyint(1) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 = received , 2 = pending, 3 = deleted'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`reg_no` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`f_name` varchar(50) NOT NULL,
`cnic1` varchar(20) DEFAULT NULL,
`cnic` varchar(20) DEFAULT NULL,
`caste` varchar(50) DEFAULT NULL,
`occupation` varchar(100) DEFAULT NULL,
`dob` date NOT NULL,
`gender` enum('M','F','O','') NOT NULL DEFAULT 'M',
`contact` varchar(13) NOT NULL,
`address` varchar(500) NOT NULL,
`admission_id` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
当前未优化的工作解决方案: 注意:我已经非常简化了 StackOverflow 的解决方案,只是为了显示问题区域
下面的 sub_query 获取所有 7 个月已缴费的学生。
$already_paid_student_ids_query = "SELECT t.student_id
FROM (
SELECT COUNT(*) AS count, student_id
FROM fee_received
WHERE fee_month IN (1, 2, 3, 4, 5, 6, 7)
AND fee_year = 2018
AND class_id = 10
AND status = 1
GROUP BY student_id
) AS t
WHERE t.count >= 7";
然后我有以下主要查询,它给了我所有拖欠费用的学生。
$fee_defaulters_query = "SELECT student.* FROM student
WHERE student.id NOT IN ( {$already_paid_student_ids_query} )";
到目前为止,我已经得到了所有的费用拖欠者。但我不知道哪个学生在哪个月拖欠学费。因此,为此,我采用了一个非常昂贵的解决方案。
我已经遍历了每个费用违约者,并请求 fee_received 表为学生获得几个月的时间。
$paid_months_query = "SELECT fee_month FROM fee_received WHERE student_id = {$student_id} AND fee_year = {$fee_year} AND fee_month IN (1,2,3,4,5,6,7) AND status = 1 ";
然后使用一些编程,我得到每个学生的未付月数并显示给管理员。
所需解决方案
我需要摆脱循环内的查询。并且需要一个解决方案,以便我在一个查询中获得所有 fee_defaulter 学生和每个学生的未付月份列表。
SQL Fiddle Link 链接
解决方案
我创建了一个 SQL Fiddle,它会生成一个缺少付款的学生列表。它还显示他们已付款的月份。
MySQL 5.6 架构设置:
CREATE TABLE `fee_received` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`challan_no` int(11) NOT NULL DEFAULT '0',
`student_id` int(11) NOT NULL,
`class_id` int(2) NOT NULL,
`amount` int(5) NOT NULL,
`arrears` int(6) NOT NULL DEFAULT '0' COMMENT 'arrears in monthly tuition fee.',
`fine` int(11) NOT NULL DEFAULT '0' COMMENT 'total fine in current challan',
`fee_month` int(2) NOT NULL,
`fee_year` int(4) NOT NULL,
`dt` date NOT NULL,
`multi_fee` tinyint(11) NOT NULL DEFAULT '0',
`submitted_with_admission` tinyint(1) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 = received , 2 = pending, 3 = deleted'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`reg_no` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`f_name` varchar(50) NOT NULL,
`cnic1` varchar(20) DEFAULT NULL,
`cnic` varchar(20) DEFAULT NULL,
`caste` varchar(50) DEFAULT NULL,
`occupation` varchar(100) DEFAULT NULL,
`dob` date NOT NULL,
`gender` enum('M','F','O','') NOT NULL DEFAULT 'M',
`contact` varchar(13) NOT NULL,
`address` varchar(500) NOT NULL,
`admission_id` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student`
(`reg_no`, `name`, `f_name`, `cnic1`, `cnic`, `caste`, `occupation`, `dob`, `gender`, `contact`, `address`, `admission_id`)
VALUES
(1123,'John Doe','John','1','1','1','student','2000-05-01','M','123-456-7890','123 S Western Ave',123),
(1533,'Jane Doe','Jane','1','1','1','student','2000-05-01','F','123-456-7890','123 S Western Ave',123),
(2341,'Fred Smith','Fred','1','1','1','student','2000-05-01','M','123-456-7890','123 S Western Ave',123),
(6541,'Tanya Edilstien','Tanya','1','1','1','student','2000-05-01','M','123-456-7890','123 S Western Ave',123);
INSERT INTO `fee_received`
(`challan_no`, `student_id`, `class_id`, `amount`, `arrears`, `fine`, `fee_month`, `fee_year`, `dt`, `multi_fee`, `submitted_with_admission`, `status`)
VALUES
(1,1,1,123,0,0,1,2018,'2018-01-04',0,0,1),
(1,1,1,123,0,0,2,2018,'2018-02-04',0,0,1),
(1,1,1,123,0,0,3,2018,'2018-03-04',0,0,1),
(1,1,1,123,0,0,4,2018,'2018-04-04',0,0,1),
(1,1,1,123,0,0,5,2018,'2018-05-04',0,0,1),
(1,1,1,123,0,0,6,2018,'2018-06-04',0,0,1),
(1,1,1,123,0,0,7,2018,'2018-07-04',0,0,1),
(2,2,2,123,0,0,1,2018,'2018-01-04',0,0,1),
(2,2,2,123,0,0,4,2018,'2018-04-04',0,0,1),
(2,2,2,123,0,0,5,2018,'2018-05-04',0,0,1),
(2,2,2,123,0,0,6,2018,'2018-06-04',0,0,1),
(2,2,2,123,0,0,7,2018,'2018-07-04',0,0,1),
(3,3,3,123,0,0,1,2018,'2018-01-04',0,0,1),
(3,3,3,123,0,0,2,2018,'2018-02-04',0,0,1),
(3,3,3,123,0,0,3,2018,'2018-03-04',0,0,1),
(3,3,3,123,0,0,4,2018,'2018-04-04',0,0,1),
(3,3,3,123,0,0,5,2018,'2018-05-04',0,0,1),
(3,3,3,123,0,0,7,2018,'2018-07-04',0,0,1),
(4,4,4,123,0,0,1,2018,'2018-01-04',0,0,1),
(4,4,4,123,0,0,2,2018,'2018-02-04',0,0,1),
(4,4,4,123,0,0,3,2018,'2018-03-04',0,0,1),
(4,4,4,123,0,0,4,2018,'2018-04-04',0,0,1),
(4,4,4,123,0,0,5,2018,'2018-05-04',0,0,1),
(4,4,4,123,0,0,6,2018,'2018-06-04',0,0,1),
(4,4,4,123,0,0,7,2018,'2018-07-04',0,0,1);
查询 1:
select
a.`id`,
a.`name`,
a.`contact`,
a.`address`,
count(b.`id`) as `num_payments`,
GROUP_CONCAT(b.`fee_month` ORDER BY b.`fee_month`) as `Months_Paid`
FROM `student` a
LEFT JOIN `fee_received` b
ON a.`id` = b.`student_id` AND b.`status` = 1 AND b.`dt` BETWEEN '2018-01-01' AND '2018-07-31'
GROUP BY a.`id`
HAVING `num_payments` < 7
结果:
| id | name | contact | address | num_payments | Months_Paid |
|----|------------|--------------|-------------------|--------------|-------------|
| 2 | Jane Doe | 123-456-7890 | 123 S Western Ave | 5 | 1,4,5,6,7 |
| 3 | Fred Smith | 123-456-7890 | 123 S Western Ave | 6 | 1,2,3,4,5,7 |
推荐阅读
- python - 在 tensorflow 和 numpy 上使用高斯模糊的不同结果
- flutter - flutter dio(4.0.0) 处理令牌过期(处理 401)
- google-sheets - 如何从另一列的单元格中引用/获取值,但该单元格位于多个空白单元格之上?看图片
- swift - 无法转换“T”类型的值?到预期的参数类型“T?”
- java - 如何为特殊的航空图(如用例)有效地使用图结构?
- powershell - VSCode - Powershell - 网络驱动器
- javascript - 下载的 Gzip 似乎已损坏(Chrome)
- c# - 如何取消任务但让她完成块?
- swift - NSSavePanel 初始化抛出 Thread 1: EXC_BREAKPOINT (code=EXC_I386_BPT, subcode=0x0) on
- typescript - Typescript:如何在原始数组和非原始数组之间键入保护数组值