从最后一项中选择信息并加入总数 [英] 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;

SQL DEMO

这篇关于从最后一项中选择信息并加入总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！