MySQL的汇总值
问题描述:
我有一个像下面MySQL的汇总值
+----+---------+-------+-----+----------+ | id | user_id | price | qty | category | +----+---------+-------+-----+----------+
,我有一个查询数据库中的表
SELECT *,(SELECT `login` FROM `users` WHERE `users`.`user_id`=`warehouse`.`user_id`) AS `login`, (`price`*`qty`) AS `total_price` FROM `warehouse`
所以我需要什么...我需要在下一个山坳总价格,其中行是同一类别的sumary
编辑
代表样品
CREATE TABLE IF NOT EXISTS `warehouse` ( `id` int(11) NOT NULL DEFAULT 0, `user_id` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL, `qty` int(11) DEFAULT NULL, `category` int(11) DEFAULT NULL ); INSERT INTO `warehouse` (`id`, `user_id`, `price`, `qty`, `category`) VALUES (1, 1, 10, 5, 1), (2, 1, 15, 2, 1), (3, 1, 8, 3, 1), (4, 1, 28, 10, 1), (5, 1, 10, 1, 2); CREATE TABLE IF NOT EXISTS `users` ( `user_id` int(11) DEFAULT NULL, `login` varchar(32) DEFAULT NULL ); INSERT INTO `users` (`user_id`, `login`) VALUES (1, 'test');
结果与我的查询
+----+---------+-------+-----+----------+-------+-------------+ | id | user_id | price | qty | category | login | total_price | +----+---------+-------+-----+----------+-------+-------------+ | 1 | 1 | 10 | 5 | 1 | test | 50 | +----+---------+-------+-----+----------+-------+-------------+ | 2 | 1 | 15 | 2 | 1 | test | 30 | +----+---------+-------+-----+----------+-------+-------------+ | 3 | 1 | 8 | 3 | 1 | test | 24 | +----+---------+-------+-----+----------+-------+-------------+ | 4 | 1 | 28 | 10 | 2 | test | 280 | +----+---------+-------+-----+----------+-------+-------------+ | 5 | 1 | 10 | 1 | 1 | test | 10 | +----+---------+-------+-----+----------+-------+-------------+
我需要什么
+----+---------+-------+-----+----------+-------+-------------+--------------------------+ | id | user_id | price | qty | category | login | total_price | sum_category_total_price | +----+---------+-------+-----+----------+-------+-------------+--------------------------+ | 1 | 1 | 10 | 5 | 1 | test | 50 | 114 | +----+---------+-------+-----+----------+-------+-------------+--------------------------+ | 2 | 1 | 15 | 2 | 1 | test | 30 | 114 | +----+---------+-------+-----+----------+-------+-------------+--------------------------+ | 3 | 1 | 8 | 3 | 1 | test | 24 | 114 | +----+---------+-------+-----+----------+-------+-------------+--------------------------+ | 4 | 1 | 28 | 10 | 2 | test | 280 | 280 | +----+---------+-------+-----+----------+-------+-------------+--------------------------+ | 5 | 1 | 10 | 1 | 1 | test | 10 | 114 | +----+---------+-------+-----+----------+-------+-------------+--------------------------+
答
我认为应该解决您的问题:
SELECT *,(SELECT `login` FROM `users` WHERE `users`.`user_id`=`warehouse`.`user_id`) AS `login`, (`price`*`qty`) AS `total_price`
FROM warehouse join (select category, sum((`price`*`qty`)) AS `total_price` from warehouse group by category) as totals on warehouse.category=totals.category
+0
完美,tnx ... – 2014-10-06 10:04:10
答
select *,(price*qty) AS total_price from Warehouse
join (select category,sum(price*qty) as sum_category_total_price from Warehouse group by category) as Temp
on Temp.category=Warehouse.category
join users
on users.user_id=warehouse.user_id
+0
这不是我所需要的,我需要total_price的摘要,其中是同一类别...在演示中是这些值50 + 200 = 250 – 2014-10-06 08:55:55
为您所标记的问题,那是不是'MySQL'语法。它看起来像'sql-server'。请更正标签。 – Jens 2014-10-06 08:13:30
我编辑过MySQL的语法,对不起 – 2014-10-06 08:46:28
您能添加一些示例数据和预期结果吗? – Jens 2014-10-06 08:48:37