一条sql语句查询某字段的数据总和,并列出表中的所有记录

阅读精选内容,加入我们投稿,点这里!

记录一下

首先,查询记录条数,可以使用count

1
select count(*) from (SELECT * FROM kk.kkhmd)

这样的结果就显示kk.kkhmd表中的记录数,而不是把数据全部查出来

1
2
3
  COUNT(*)
----------
        51

但是如果使用一条sql语句查询某字段的数据总和,并列出表中的所有记录 怎么做呢?

以下是一个完整示例:

先创建一张表,演示方便

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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);

 

1
2
3
4
5
6
7
8
9
10
SELECT
	a.*, b.*
FROM
	consume a,
	(
		SELECT
			sum(monetary)
		FROM
			consume
	) b;

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
	a.*, b.*
FROM
	consume a,
	(
		SELECT
			sum(monetary),
			product
		FROM
			consume
		GROUP BY
			product
	) b
WHERE
	a.product = b.product;

 

发表评论