使用分页(PHP，MySQL)时，计算运行总计/余额的最有效方法是什么? [英] What's the most efficient way to calculate a running total/balance when using pagination (PHP, MySQL)
问题描述
我有一个MySQL表，用于存储员工记录的里程记录.我有一个PHP页面，该表在一个表(最新到最旧)中输出每个员工的里程记录以及运行余额.一切正常.
I have a MySQL table that stores mileage records logged by employees. I have a PHP page that outputs the mileage records for each employee in a table (newest to oldest) along with a running balance. This all works fine.
里程记录的增长速度超出了预期，现在很明显需要分页.我已经对分页进行了编程，效果很好.我现在遇到的问题是，分页忽略了我选择的记录之外的任何值，从而破坏了"运行余额.例如，假设您的银行帐户仅提供了7月的余额，而没有考虑6月帐户中的余额.
The mileage records are growing faster than anticipated and it's now become apparent that pagination is required. I have programmed the pagination and this works fine. The problem I now have is that the pagination has 'broken' the running balance in the way that it ignores any values outside of the records that I have selected. For example, imagine your bank account just provided a balance for the month of July without taking into account what was in your account in June.
由于Google不会提供太多帮助，因此在理论上如何解决此问题的建议(我不需要一行一行的代码)将不胜感激.
Suggestions on how to tackle this problem in theory (I don't need line by line code) would be much appreciated as Google isn't throwing up much help.
编辑1
Database and data
-- phpMyAdmin SQL Dump
-- version 4.0.10.14
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Jul 21, 2016 at 07:11 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `propsyst_main`
--
-- --------------------------------------------------------
--
-- Table structure for table `employee_mileage`
--
CREATE TABLE IF NOT EXISTS `employee_mileage` (
`employee_mileage_id` int(11) NOT NULL AUTO_INCREMENT,
`employee_mileage_employee_id` int(11) DEFAULT NULL,
`employee_mileage_vehicle_id` smallint(6) DEFAULT NULL,
`employee_mileage_start_postcode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`employee_mileage_end_postcode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`employee_mileage_mileage` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`employee_mileage_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8 ;
--
-- Dumping data for table `employee_mileage`
--
INSERT INTO `employee_mileage` (`employee_mileage_id`, `employee_mileage_employee_id`, `employee_mileage_vehicle_id`, `employee_mileage_start_postcode`, `employee_mileage_end_postcode`, `employee_mileage_mileage`) VALUES
(1, 1, 2, 'L17 0BZ', 'L36 9TJ', '1.00'),
(2, 1, 2, 'L17 0BZ', 'L36 9TJ', '2.00'),
(3, 1, 2, 'L17 0BZ', 'L36 9TJ', '3.00'),
(4, 1, 2, 'L17 0BZ', 'L36 9TJ', '4.00'),
(5, 1, 2, 'L17 0BZ', 'L36 9TJ', '5.00'),
(6, 1, 2, 'L17 0BZ', 'L36 9TJ', '6.00'),
(7, 1, 2, 'L17 0BZ', 'L36 9TJ', '7.00');
编辑2
查询尝试不起作用；
$statement = "SELECT *
from (
SELECT em.*, e.*,
@balance := @balance + em.employee_mileage_mileage as balance
FROM employee_mileage em
CROSS JOIN (select @balance := 0) init
INNER JOIN employee e
on em.employee_mileage_employee_id = e.employee_id
where em.employee_mileage_employee_id = " . $employee_id . "
order by em.employee_mileage_id
) as base
ORDER BY em.employee_mileage_id DESC";
$employee_mileage_query = mysqli_query($con,"{$statement} LIMIT {$startpoint} , {$per_page}") or die(mysql_error());
编辑3
数据库；
-- phpMyAdmin SQL Dump
-- version 4.0.10.14
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Jul 25, 2016 at 10:22 PM
-- Server version: 5.1.73-cll
-- PHP Version: 5.4.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `propsyst_main`
--
-- --------------------------------------------------------
--
-- Table structure for table `employee_mileage`
--
CREATE TABLE IF NOT EXISTS `employee_mileage` (
`employee_mileage_id` int(11) NOT NULL AUTO_INCREMENT,
`employee_mileage_employee_id` int(11) DEFAULT NULL,
`employee_mileage_vehicle_id` smallint(6) DEFAULT NULL,
`employee_mileage_journey_date` date DEFAULT NULL,
`employee_mileage_start_postcode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`employee_mileage_end_postcode` varchar(8) COLLATE utf8_bin DEFAULT NULL,
`employee_mileage_mileage` decimal(6,2) DEFAULT NULL,
`employee_mileage_date_created` datetime DEFAULT NULL,
`employee_mileage_created_by` int(11) DEFAULT NULL,
PRIMARY KEY (`employee_mileage_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;
--
-- Dumping data for table `employee_mileage`
--
INSERT INTO `employee_mileage` (`employee_mileage_id`, `employee_mileage_employee_id`, `employee_mileage_vehicle_id`, `employee_mileage_journey_date`, `employee_mileage_start_postcode`, `employee_mileage_end_postcode`, `employee_mileage_mileage`, `employee_mileage_date_created`, `employee_mileage_created_by`) VALUES
(1, 32, 1, '2016-07-15', 'L17 0BZ', 'L6 5BJ', '4.19', '2016-07-25 10:15:01', 32),
(2, 32, 1, '2016-07-15', 'L6 5BJ', 'L17 0BZ', '4.19', '2016-07-25 10:15:01', 32),
(5, 32, 1, '2016-07-23', 'L17 0BZ', 'L17 1AE', '1.55', '2016-07-25 12:14:15', 32),
(3, 32, 1, '2016-07-21', 'L17 0BZ', 'L19 0PD', '2.03', '2016-07-25 12:09:24', 32),
(4, 32, 1, '2016-07-21', 'L19 0PD', 'L17 0BZ', '2.03', '2016-07-25 12:09:24', 32),
(6, 32, 1, '2016-07-23', 'L17 1AE', 'L17 0BZ', '1.55', '2016-07-25 12:14:15', 32);
查询；
$statement = "SELECT *
from (
SELECT em.*, e.*, v.*,
@balance := @balance + em.employee_mileage_mileage as balance
FROM employee_mileage em
CROSS JOIN (select @balance := 0) init
INNER JOIN employee e
on em.employee_mileage_employee_id = e.employee_id
INNER JOIN vehicle v
on em.employee_mileage_vehicle_id = v.vehicle_id
WHERE em.employee_mileage_employee_id = " . $employee_id . "
ORDER BY em.employee_mileage_id
) as base
ORDER BY base.employee_mileage_id DESC";
$employee_mileage_query = mysqli_query($con,"{$statement} LIMIT {$startpoint} , {$per_page}") or die(mysql_error());
输出；
推荐答案
我建议您在SQL查询中计算运行余额.
I would suggest calculating the running balance in your SQL query.
以下是该SQL语句的示例:
Here is an example of what that SQL statement could look like:
SELECT *
from (
SELECT *,
@balance := @balance + em.employee_mileage_mileage as balance
FROM (select em.*, e.*, v.*
from employee_mileage em
INNER JOIN employee e
on em.employee_mileage_employee_id = e.employee_id
INNER JOIN vehicle v
on em.employee_mileage_vehicle_id = v.vehicle_id
WHERE em.employee_mileage_employee_id = ?
ORDER BY em.employee_mileage_id ASC
) em
CROSS JOIN (select @balance := 0) init
) as base
ORDER BY base.employee_mileage_id DESC
LIMIT ?, 2
以上语句中的参数(标记为?)应绑定到:
The parameters in the above statement (marked with ?) should be bound to:
-
您要显示其数据的
- employee_id
- 当前页面的开始行(从零开始)
- the employee_id you want to show the data for
- the start row of the current page (zero-based)
内部查询遍历该特定员工的所有行，并将运行余额添加到每条记录.然后，外部查询会反转排序顺序，并将限制应用于分页.
The inner query goes through all rows for that particular employee and adds the running balance to every record. Then the outer query reverses the sort order and applies the limit to it for paging purposes.
此解决方案无需在PHP中进行任何计算；在查询结果集中可以很容易地找到运行余额，并且在每个页面上都正确.
This solution needs no calculation to happen in PHP; the running balance is readily available in the query result set, and is correct on every page.
这里有一个很小的 SQL提琴演示了它如何获取第二页，其中包含2每页行数，总共6行，包括正确的余额.
Here is a small SQL fiddle demonstrating it for getting the second page, with 2 rows per page, from a total set of 6 rows, including the correct balance.
这篇关于使用分页(PHP，MySQL)时，计算运行总计/余额的最有效方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
