表导出的SQL是这样的:
-- phpMyAdmin SQL Dump
-- version 2.10.3
-- http://www.phpmyadmin.net
--
-- 主机: localhost:3306
-- 生成日期: 2008 年 10 月 16 日 14:13
-- 服务器版本: 5.1.26
-- PHP 版本: 5.2.6SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `dongli`
-- -- ----------------------------------------------------------
-- 表的结构 `logs`
-- CREATE TABLE `logs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`piaohao` varchar(15) NOT NULL,
`cardNo` varchar(20) NOT NULL,
`happenTime` datetime NOT NULL,
`operationType` int(11) NOT NULL,
`contents` varchar(40) NOT NULL,
`transactor` varchar(20) NOT NULL,
`productionNo` varchar(32) NOT NULL,
`productionName` varchar(40) NOT NULL,
`productionCount` int(11) NOT NULL,
`productionBeginCount` int(11) NOT NULL,
`productionEndCount` int(11) NOT NULL,
`re` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `piaohao` (`piaohao`),
KEY `cardNo` (`cardNo`),
KEY `transactor` (`transactor`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=57 ;--
-- 导出表中的数据 `logs`
-- INSERT INTO `logs` VALUES (1, '2008000024', 'keke', '2008-09-19 10:25:00', 2, '提货', 'keke', 'A001', '氧气(10Kg)', 1, 8, 7, '提货');
INSERT INTO `logs` VALUES (2, '2008000025', 'keke', '2008-09-19 10:29:29', 2, '提货', 'keke', 'A001', '氧气(10Kg)', 2, 7, 5, '提货');
INSERT INTO `logs` VALUES (3, '2008000026', 'keke', '2008-09-19 10:30:09', 2, '提货', 'keke', 'A001', '氧气(10Kg)', 1, 5, 4, '提货');
INSERT INTO `logs` VALUES (4, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'A002', '氧气(15Kg)', 1, 13, 12, '提货');
INSERT INTO `logs` VALUES (5, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'A003', '氧气(50Kg)', 1, 27, 26, '提货');
INSERT INTO `logs` VALUES (6, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'B001', '氮气(30kg)', 1, 33, 32, '提货');
INSERT INTO `logs` VALUES (7, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'B002', '氮气(15Kg)', 1, 44, 43, '提货');
INSERT INTO `logs` VALUES (8, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'C001', '二氧化碳(30Kg)', 1, 49, 48, '提货');
INSERT INTO `logs` VALUES (9, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'D001', '氩气(10Kg)', 1, 65, 64, '提货');
INSERT INTO `logs` VALUES (10, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'D002', '氩气(20Kg)', 1, 72, 71, '提货');
INSERT INTO `logs` VALUES (11, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'E001', '液氧(40Kg)', 1, 73, 72, '提货');
INSERT INTO `logs` VALUES (12, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'A003', '氧气(50Kg)', 1, 26, 25, '提货');
INSERT INTO `logs` VALUES (13, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'B001', '氮气(30kg)', 1, 32, 31, '提货');
INSERT INTO `logs` VALUES (14, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'C001', '二氧化碳(30Kg)', 1, 48, 47, '提货');
INSERT INTO `logs` VALUES (15, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'D001', '氩气(10Kg)', 1, 64, 63, '提货');
INSERT INTO `logs` VALUES (16, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'E001', '液氧(40Kg)', 1, 72, 71, '提货');
INSERT INTO `logs` VALUES (17, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'A001', '氧气(10Kg)', 96, 4, 100, '充值');
INSERT INTO `logs` VALUES (18, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'A002', '氧气(15Kg)', 88, 12, 100, '充值');
INSERT INTO `logs` VALUES (19, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'A003', '氧气(50Kg)', 75, 25, 100, '充值');
INSERT INTO `logs` VALUES (20, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'B001', '氮气(30kg)', 69, 31, 100, '充值');
INSERT INTO `logs` VALUES (21, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'B002', '氮气(15Kg)', 57, 43, 100, '充值');
INSERT INTO `logs` VALUES (22, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'C001', '二氧化碳(30Kg)', 53, 47, 100, '充值');
INSERT INTO `logs` VALUES (23, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'D001', '氩气(10Kg)', 37, 63, 100, '充值');
INSERT INTO `logs` VALUES (24, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'D002', '氩气(20Kg)', 29, 71, 100, '充值');
INSERT INTO `logs` VALUES (55, '2008000024', '2', '2008-10-16 14:00:55', 1, '充值', 'keke', 'A001', '氧气(10Kg)', 10, 0, 10, '充值');
INSERT INTO `logs` VALUES (56, '2008000025', '2', '2008-10-16 14:06:15', 1, '充值', 'keke', 'A001', '氧气(10Kg)', 10, 10, 20, '充值');现在我想查询截止到某一时间,各个卡号,产品余额是多少! 此表是一个流水记账表,以上字段的含义:
`id` id,
`piaohao` 发票号 ,
`cardNo` 卡号,
`happenTime` 发生时间,
`operationType` 操作类别,充值还是消费,
`contents` 内容,
`transactor` 操作者,
`productionNo` 产品编号,
`productionName` 产品名称,
`productionCount` 发生数量,
`productionBeginCount` 初始额,
`productionEndCount` 余额,-----------要知道的就在这里
`re` 备注
-- phpMyAdmin SQL Dump
-- version 2.10.3
-- http://www.phpmyadmin.net
--
-- 主机: localhost:3306
-- 生成日期: 2008 年 10 月 16 日 14:13
-- 服务器版本: 5.1.26
-- PHP 版本: 5.2.6SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";--
-- 数据库: `dongli`
-- -- ----------------------------------------------------------
-- 表的结构 `logs`
-- CREATE TABLE `logs` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`piaohao` varchar(15) NOT NULL,
`cardNo` varchar(20) NOT NULL,
`happenTime` datetime NOT NULL,
`operationType` int(11) NOT NULL,
`contents` varchar(40) NOT NULL,
`transactor` varchar(20) NOT NULL,
`productionNo` varchar(32) NOT NULL,
`productionName` varchar(40) NOT NULL,
`productionCount` int(11) NOT NULL,
`productionBeginCount` int(11) NOT NULL,
`productionEndCount` int(11) NOT NULL,
`re` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `piaohao` (`piaohao`),
KEY `cardNo` (`cardNo`),
KEY `transactor` (`transactor`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=57 ;--
-- 导出表中的数据 `logs`
-- INSERT INTO `logs` VALUES (1, '2008000024', 'keke', '2008-09-19 10:25:00', 2, '提货', 'keke', 'A001', '氧气(10Kg)', 1, 8, 7, '提货');
INSERT INTO `logs` VALUES (2, '2008000025', 'keke', '2008-09-19 10:29:29', 2, '提货', 'keke', 'A001', '氧气(10Kg)', 2, 7, 5, '提货');
INSERT INTO `logs` VALUES (3, '2008000026', 'keke', '2008-09-19 10:30:09', 2, '提货', 'keke', 'A001', '氧气(10Kg)', 1, 5, 4, '提货');
INSERT INTO `logs` VALUES (4, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'A002', '氧气(15Kg)', 1, 13, 12, '提货');
INSERT INTO `logs` VALUES (5, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'A003', '氧气(50Kg)', 1, 27, 26, '提货');
INSERT INTO `logs` VALUES (6, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'B001', '氮气(30kg)', 1, 33, 32, '提货');
INSERT INTO `logs` VALUES (7, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'B002', '氮气(15Kg)', 1, 44, 43, '提货');
INSERT INTO `logs` VALUES (8, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'C001', '二氧化碳(30Kg)', 1, 49, 48, '提货');
INSERT INTO `logs` VALUES (9, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'D001', '氩气(10Kg)', 1, 65, 64, '提货');
INSERT INTO `logs` VALUES (10, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'D002', '氩气(20Kg)', 1, 72, 71, '提货');
INSERT INTO `logs` VALUES (11, '2008000026', 'keke', '2008-09-19 10:30:10', 2, '提货', 'keke', 'E001', '液氧(40Kg)', 1, 73, 72, '提货');
INSERT INTO `logs` VALUES (12, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'A003', '氧气(50Kg)', 1, 26, 25, '提货');
INSERT INTO `logs` VALUES (13, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'B001', '氮气(30kg)', 1, 32, 31, '提货');
INSERT INTO `logs` VALUES (14, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'C001', '二氧化碳(30Kg)', 1, 48, 47, '提货');
INSERT INTO `logs` VALUES (15, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'D001', '氩气(10Kg)', 1, 64, 63, '提货');
INSERT INTO `logs` VALUES (16, '2008000027', 'keke', '2008-09-19 10:31:24', 2, '提货', 'keke', 'E001', '液氧(40Kg)', 1, 72, 71, '提货');
INSERT INTO `logs` VALUES (17, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'A001', '氧气(10Kg)', 96, 4, 100, '充值');
INSERT INTO `logs` VALUES (18, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'A002', '氧气(15Kg)', 88, 12, 100, '充值');
INSERT INTO `logs` VALUES (19, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'A003', '氧气(50Kg)', 75, 25, 100, '充值');
INSERT INTO `logs` VALUES (20, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'B001', '氮气(30kg)', 69, 31, 100, '充值');
INSERT INTO `logs` VALUES (21, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'B002', '氮气(15Kg)', 57, 43, 100, '充值');
INSERT INTO `logs` VALUES (22, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'C001', '二氧化碳(30Kg)', 53, 47, 100, '充值');
INSERT INTO `logs` VALUES (23, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'D001', '氩气(10Kg)', 37, 63, 100, '充值');
INSERT INTO `logs` VALUES (24, '2008000023', 'keke', '2008-09-19 10:33:53', 1, '充值', 'keke', 'D002', '氩气(20Kg)', 29, 71, 100, '充值');
INSERT INTO `logs` VALUES (55, '2008000024', '2', '2008-10-16 14:00:55', 1, '充值', 'keke', 'A001', '氧气(10Kg)', 10, 0, 10, '充值');
INSERT INTO `logs` VALUES (56, '2008000025', '2', '2008-10-16 14:06:15', 1, '充值', 'keke', 'A001', '氧气(10Kg)', 10, 10, 20, '充值');现在我想查询截止到某一时间,各个卡号,产品余额是多少! 此表是一个流水记账表,以上字段的含义:
`id` id,
`piaohao` 发票号 ,
`cardNo` 卡号,
`happenTime` 发生时间,
`operationType` 操作类别,充值还是消费,
`contents` 内容,
`transactor` 操作者,
`productionNo` 产品编号,
`productionName` 产品名称,
`productionCount` 发生数量,
`productionBeginCount` 初始额,
`productionEndCount` 余额,-----------要知道的就在这里
`re` 备注
select cardNo, productEndCount from T a where happenTime = (select max(happendTime) from T b where b.cardNo=a.cardNo and b.happenTime<='2008-10-16 14:06:15')最后的时间是你要的时间
select a.* from logs a
inner join
(select cardNo,productionNo,max(happenTime) as ms from logs group by cardNo,productionNo) b
on a.cardNo=b.cardNo and a.productionNo=b.productionNo and a.happenTime=b.ms
卡号 产品编号 产品名称 余额
keke A001 氧气(10Kg) 100
keke A002 氧气(15Kg) 100
keke A003 氧气(50Kg) 100
keke B001 氮气(30kg) 100
keke B002 氮气(15Kg) 100
keke C001 二氧化碳(30Kg) 100
keke D001 氩气(10Kg) 100
keke D002 氩气(20Kg) 100
keke E001 液氧(40Kg) 71
2 A001 氧气(10Kg) 10
inner join
(select cardNo,productionNo,max(happenTime) as ms from logs WHERE happenTime <='2008-10-16 14:06:15' group by cardNo,productionNo) b
on a.cardNo=b.cardNo and a.productionNo=b.productionNo and a.happenTime=b.ms2 A001 氧气(10Kg) 10 应该为20吧?
您的答案正确,谢谢。SELECT a . *
FROM LOGS a
INNER JOIN (SELECT cardNo, productionNo, max( happenTime ) AS ms
FROM LOGS WHERE happentime < '2008-10-16 14:06:00'
GROUP BY cardNo, productionNo
)b ON a.cardNo = b.cardNo
AND a.productionNo = b.productionNo
AND a.happenTime = b.ms