从最后一项中选择信息并加入总数 [英] Select information from last item and join to the total amount
本文介绍了从最后一项中选择信息并加入总数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有一个这样的表:
Suppose I have a table like this:
id |日期|客户|出售
id | dates | customer | sale
并要为每个客户选择:
- 购买数量
- 最后购买价值
- 购买总额(具有正值)
使用此表定义:
CREATE TABLE sales (
id int auto_increment primary key,
dates date,
customer int,
sale int
);
此数据:
INSERT INTO sales
(dates, customer, sale)
VALUES
('2012-01-01', 1, 2),
('2012-02-01', 1, 8),
('2012-03-01', 2, 1),
('2012-04-01', 2, 7),
('2012-05-01', 2, -5),
('2012-06-01', 1, 5)
我的结果应该是:
customer | sales | last_sale
1 3 5
2 3 7
我不知道如何获得它.我已经达到了:
I cannot figure out how to get it. I have reached this:
SELECT s.customer, COUNT(s.sale) total_sales, last_sale FROM sales AS s
JOIN
(SELECT customer, sale last_sale FROM sales GROUP BY customer ORDER BY dates DESC) AS t
ON t.customer=s.customer
GROUP BY s.customer
但是它不起作用.关于如何获取此数据的任何想法?
But it is not working. Any ideas of how to get this data?
所有代码都在 SQL提琴
推荐答案
尝试一下
SELECT a.customer, count(a.sale), max_sale
FROM sales a
INNER JOIN (SELECT customer, sale max_sale
from sales x where dates = (select max(dates)
from sales y
where x.customer = y.customer
and y.sale > 0
)
)b
ON a.customer = b.customer
GROUP BY a.customer, max_sale;
这篇关于从最后一项中选择信息并加入总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文