56,677
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `consume` (
`ID` int(6) NOT NULL auto_increment,
`user` varchar(64) DEFAULT NULL,
`product` varchar(64) DEFAULT NULL,
`time` DATETIME,
`monetary` int(6) DEFAULT -1,
PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;
INSERT INTO consume(user,product,time,monetary) values('user1','iphone',STR_TO_DATE('2018-09-05 10:17:00', '%Y-%m-%d %H:%i:%s') ,10000);
INSERT INTO consume(user,product,time,monetary) values('user2','iphone3',STR_TO_DATE('2013-09-05 10:17:00', '%Y-%m-%d %H:%i:%s') ,90000);
INSERT INTO consume(user,product,time,monetary) values('user3','iphone4',STR_TO_DATE('2014-09-05 10:17:00', '%Y-%m-%d %H:%i:%s') ,70000);
INSERT INTO consume(user,product,time,monetary) values('user4','iphone5',STR_TO_DATE('2015-09-05 10:17:00', '%Y-%m-%d %H:%i:%s') ,80000);
INSERT INTO consume(user,product,time,monetary) values('user5','iphone6',STR_TO_DATE('2016-09-05 10:17:00', '%Y-%m-%d %H:%i:%s') ,60000);
INSERT INTO consume(user,product,time,monetary) values('user6','iphone8',STR_TO_DATE('2017-09-05 10:17:00', '%Y-%m-%d %H:%i:%s') ,90000);
SELECT
a.*, b.*
FROM
consume a,
(
SELECT
sum(monetary)
FROM
consume
) b;
SELECT
a.*, b.*
FROM
consume a,
(
SELECT
sum(monetary),
product
FROM
consume
GROUP BY
product
) b
WHERE
a.product = b.product;