php - 使用 php 获取上次输入日期的总价格的 SQL 查询

Question

我正在使用 php。我想使用查询获取上次输入日期的总价。我使用连接查询来连接 3 个表。在第一个表中存储用户详细信息 oc_user，第二个表(oc_postable)包含产品的发票号和 refid(产品输入在一起具有相同的发票号码和 refid)，第三个表(oc_addcart)包含每个产品详细信息，价格，refid，用户 ID，日期。我使用了以下查询

SELECT a.userid,FROM_UNIXTIME(max(a.addedtime)) as addedtime,sum(price) as totalamnt from `oc_addtocart` a,`oc_postable` b,`oc_user` as c where a.status=1 and c.user_id=a.userid AND b.`ticketrefid` = a.refid group by a.refid ORDER BY c.user_id ASC LIMIT 0,20 

我得到的输出为

userid  addedtime   totalamnt
1   2015-04-24 10:40:56     679.99
1   2015-05-07 15:30:44     2700
1   2015-04-24 11:00:08     100
1   2015-05-07 14:55:05     400
1   2015-04-24 09:24:35     230
1   2015-05-07 15:01:59     679.99
5   2015-05-08 12:19:41     430

我想得到输出

userid  addedtime   totalamnt
 1  2015-05-07 15:30:44     2700
 5  2015-05-08 12:19:41     430

oc_user表结构

CREATE TABLE IF NOT EXISTS `oc_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_group_id` int(11) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(40) NOT NULL,
  `salt` varchar(9) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(96) NOT NULL,
  `image` varchar(255) NOT NULL,
  `code` varchar(40) NOT NULL,
  `ip` varchar(40) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `date_added` datetime NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

oc_postable表结构

CREATE TABLE IF NOT EXISTS `oc_postable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_no` varchar(20) NOT NULL,
  `table_status` int(10) NOT NULL,
  `ticket_designator` varchar(10) NOT NULL,
  `table_position` int(30) NOT NULL,
  `invoice_no` varchar(100) NOT NULL,
  `ticketrefid` varchar(30) NOT NULL,
  `split_no` int(25) NOT NULL DEFAULT '0',
  `user` int(30) NOT NULL DEFAULT '1',
  `payment_method` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

oc_addcart表结构

CREATE TABLE IF NOT EXISTS `oc_addtocart` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `userid` int(25) NOT NULL,
  `prodid` int(25) NOT NULL,
  `options_selected` varchar(50) NOT NULL,
  `notes` longtext NOT NULL,
  `status` int(10) NOT NULL,
  `addedtime` varchar(25) NOT NULL,
  `refid` varchar(25) NOT NULL,
  `togo` int(10) NOT NULL DEFAULT '0',
  `price` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

请提出解决方案。等待您的回复。

Answer 1

一种方法是使用子查询:

SELECT A.USERID, A.ADDEDTIME AS ADDEDTIME, A.PRICE AS TOTALAMT 
FROM OC_ADDTOCART A 
INNER JOIN OC_USER C ON A.USERID = C.USER_ID
INNER JOIN OC_POSTABLE B ON A.REFID = B.TICKETREFID
WHERE A.STATUS =1
AND A.ADDEDTIME = 
(SELECT MAX(ADDEDTIME) ADT FROM OC_ADDTOCART WHERE USERID = A.USERID) 
ORDER BY C.USER_ID ASC LIMIT 0,20`