我有一个用户表,订阅包和各种用户订阅。
我需要获取所有订阅成本的总和,并显示最新/最后一次订阅。最新订阅是订阅
拥有最高的订阅id。如何编写查询?我的桌子列在下面。
用户表
user_id name
1 John
2 Jane
3 Matthew
订阅包表
package_id package_name
1 Basic
2 Advanced
3 Premium
用户订阅
subscription_id user_id package_id subscription_cost date
1 1 1 2 2014-04-01
2 2 1 2 2014-04-01
3 3 1 2 2014-04-01
4 1 1 2 2014-05-01
5 1 2 3.5 2014-06-01
6 2 2 3.5 2014-06-01
7 2 2 3.5 2014-07-01
8 1 3 5 2014-07-01
9 3 2 5 2014-07-01
10 2 2 3.5 2014-08-01
11 1 1 2 2014-08-01
我的结果应该是这样的
name total_costs latest_package
John 14.5 Basic
Jane 12.5 Advanced
Matthew 7 Premium
最佳答案
因为您无论如何都需要进行聚合,所以我将采用group_concat()
/substring_index()
技巧:
select u.user_id, u.name, sum(subscription_cost) as total_costs,
substring_index(group_concat(p.package_name order by us.date desc), ',', 1) as latest_package
from usersubscriptions us join
users u
on us.user_id = u.user_id join
packages p
on us.package_id = p.package_id
group by u.user_id;
这假设没有包名有逗号。它还受到对
group_concat()
结果长度的默认限制,但这种方法通常在实践中有效。