我在做订单统计的时候遇到问题,就是多表求和时发现不正确
我有下面两个表
--
-- 表的结构 `mobile_shops_orders`
--
CREATE TABLE IF NOT EXISTS `mobile_shops_orders` (
`oid` varchar(80) NOT NULL,
`userid` int(10) NOT NULL,
`pid` int(10) NOT NULL DEFAULT '0',
`paytype` tinyint(2) NOT NULL DEFAULT '1',
`cartcount` int(10) NOT NULL DEFAULT '0',
`dprice` float(13,2) NOT NULL DEFAULT '0.00',
`price` float(13,2) NOT NULL DEFAULT '0.00',
`priceCount` float(13,2) NOT NULL,
`state` tinyint(1) NOT NULL DEFAULT '0',
`ip` char(15) NOT NULL DEFAULT '',
`stime` int(10) NOT NULL DEFAULT '0',
`rebateCount` float NOT NULL COMMENT '累计优惠价格',
KEY `stime` (`stime`),
KEY `userid` (`userid`),
KEY `oid` (`oid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `mobile_shops_orders`
--
INSERT INTO `mobile_shops_orders` (`oid`, `userid`, `pid`, `paytype`, `cartcount`, `dprice`, `price`, `priceCount`, `state`, `ip`, `stime`, `rebateCount`) VALUES
('2015031710015621508', 1, 1, 1, 8, 10.00, 728.00, 728.00, 0, '127.0.0.1', 1426561489, 10),
('2015031711044980920', 1, 1, 1, 19, 10.00, 855.50, 865.50, 0, '127.0.0.1', 1426581574, 0),
('2015031716393491386', 1, 1, 1, 8, 10.00, 600.00, 610.00, 0, '127.0.0.1', 1426588329, 0),
('2015031809153611521', 2, 1, 1, 6, 10.00, 322.00, 332.00, 0, '127.0.0.1', 1426641353, 0),
('2015031809155386138', 2, 1, 1, 3, 10.00, 30.00, 40.00, 0, '127.0.0.1', 1426641377, 0);
--
-- 数据库: `mobile`
--
-- --------------------------------------------------------
--
-- 表的结构 `mobile_shops_products`
--
CREATE TABLE IF NOT EXISTS `mobile_shops_products` (
`aid` mediumint(8) NOT NULL DEFAULT '0',
`oid` varchar(80) NOT NULL DEFAULT '',
`userid` int(10) NOT NULL,
`title` varchar(80) NOT NULL DEFAULT '',
`price` float(13,2) NOT NULL DEFAULT '0.00',
`buynum` int(10) NOT NULL DEFAULT '9',
KEY `oid` (`oid`),
KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- 转存表中的数据 `mobile_shops_products`
--
INSERT INTO `mobile_shops_products` (`aid`, `oid`, `userid`, `title`, `price`, `buynum`) VALUES
(8, '2015031710015621508', 1, '盛盈汇贵妃鸡 光鸡约3.5斤', 139.00, 5),
(9, '2015031710015621508', 1, '紫玉淮山 5斤/份', 11.00, 3),
(14, '2015031711044980920', 1, '紫玉淮山 5斤/份aaaaaaaaa', 12.50, 7),
(8, '2015031711044980920', 1, '盛盈汇贵妃鸡 光鸡约3.5斤', 139.00, 5),
(9, '2015031711044980920', 1, '紫玉淮山 5斤/份', 11.00, 3),
(10, '2015031711044980920', 1, '盛盈汇贵妃鸡 光鸡约', 10.00, 4),
(8, '2015031716393491386', 1, '盛盈汇贵妃鸡 光鸡约3.5斤', 139.00, 4),
(9, '2015031716393491386', 1, '紫玉淮山 5斤/份', 11.00, 4),
(8, '2015031809153611521', 2, '盛盈汇贵妃鸡 光鸡约3.5斤', 139.00, 2),
(9, '2015031809153611521', 2, '紫玉淮山 5斤/份', 11.00, 4),
(10, '2015031809155386138', 2, '盛盈汇贵妃鸡 光鸡约', 10.00, 3);
平时的统计是这样用的, userid 是表示当前用户
SELECT oid , SUM( priceCount ) AS count_price
FROM mobile_shops_orders AS s
WHERE s.userid = '2'
oid | count_price |
---|---|
2015031809153611521 | 372.00 |
这里的值372是我们想要的
多表查询时统计时是下面这样的
SELECT s.oid,p.oid AS poid, SUM(priceCount) AS count_price
FROM mobile_shops_orders AS s
LEFT JOIN mobile_shops_products AS p ON p.oid = s.oid
WHERE s.userid = '2'