我有一个 MySQL 表,用于存储员工记录的里程记录。我有一个 PHP 页面,它在表中输出每个员工的里程记录(从最新到最旧)以及运行余额。这一切都很好。
里程记录的增长速度比预期的要快,现在很明显需要分页。我已经编写了分页程序,效果很好。我现在遇到的问题是分页已经“破坏”了运行平衡,因为它忽略了我选择的记录之外的任何值。例如,假设您的银行帐户刚刚提供了 7 月份的余额,而没有考虑您的帐户 6 月份的余额。
关于如何在理论上解决这个问题的建议(我不需要逐行代码)将非常感激,因为谷歌没有提供太多帮助。
EDIT 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');
EDIT 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());
EDIT 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);
Query;
$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());
Output;