分组后取最后一条记录
【问题】
I have a table of users,subscription packages and various user subscriptions. I need to fetch a sum of all subscription cost and display the latest/last subscription. The latest subscription is the subscription with the highest subscription_id. How can I write my query? My tables are listed as below.
Users table
user_id name
1 John
2 Jane
3 Matthew
Subscription Packages table
package_id package_name
1 Basic
2 Advanced
3 Premium
User Subscriptions
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
My results should be like so
name total_costs latest_package
John 14.5 Basic
Jane 12.5 Advanced
Matthew 7 Premium
【回答】
MySQL 没有窗口函数，复杂有序运算难以实现。这种情况可以用 SPL 实现，计算过程更直观：
A |
|
1 |
$select us.subscription_id subscription_id,us.subscription_cost subscription_cost,u.user_id user_id,u.name name,sp.package_name package_name from User_Subscriptions us left join Users u on us.user_id=u.user_id left join Subscription_Packages sp on us.package_id=sp.package_id order by us.subscription_id |
2 |
=A1.group(user_id;name,~.sum(subscription_cost):total_costs,~.m(-1).package_name:latest_package ) |
集算器提供 JDBC 接口，可以像数据库一样嵌入到应用程序中，用起来很简单，可参考Java 如何调用 SPL 脚本。
