mysql - 从具有单个查询功能的 3 个表之间的关系导出数据

Question

我写是因为我有 3 个表

1. 客户(身份证、名字、姓氏)
2. Sales1 (id, customer_id, price, sale_date)
3. Sales2(id、customer_id、价格、销售日期)

我需要做一个查询，按客户只导出 1 行，包括销售数量、销售总和(那两个表)和上次销售日期。所有这些东西在两个日期之间导出。

我试过联合，但我无法获取客户数据

Select *
from sales1
where sale_date >= DATE(DATE_FORMAT('2001-04-1','%Y-%m-%d'))
      and sale_date <= DATE(DATE_FORMAT('2010-04-1','%Y-%m-%d'))
union
Select *
from sales2
where sale_date >= DATE(DATE_FORMAT('2001-04-1','%Y-%m-%d'))
      and sale_date <= DATE(DATE_FORMAT('2010-04-1','%Y-%m-%d')) 

在此之后，我尝试了销售表之间的内部联接，但我得到了一个包含重复值的表

Select * 
from customer
inner join sales1 on customer.cliente_id = sales1.cliente_id 
inner join sales2 on customer.cliente_id = sales2.cliente_id 
where (sales1.sale_date >= DATE(DATE_FORMAT('2001-04-1','%Y-%m-%d'))
       and sales1.sale_date <= DATE(DATE_FORMAT('2010-04-1','%Y-%m-%d')))
       and (sales2.sale_date >= DATE(DATE_FORMAT('2001-04-1','%Y-%m-%d'))
       and sales2.sale_date <= DATE(DATE_FORMAT('2010-04-1','%Y-%m-%d')));

我有两个问题:

1. 编写查询以获取所有正确的数据
2. 在查询中添加一个函数，该函数仅按客户行导出两个表中的销售数量、价格总和和最后销售日期。

我需要在一个查询中从 MySQL 导出(这是一个挑战)。请原谅我糟糕的英语。

Answer 1

我相信你要找的是这个:

SELECT
    sales.id,
    COUNT(sales.sales_id) AS sales_count,
    SUM(sales.sales_price) AS price_sum,
    MAX(sales.sales_date) AS last_sale
FROM
    (SELECT
        c.id,
        s1.id AS sales_id,
        s1.price AS sales_price,
        s1.sale_date AS sales_date
     FROM
        customer c
        INNER JOIN sales1 s1 ON c.id = s1.customer_id
     UNION
     SELECT
       c.id,
       s2.id AS sales_id,
       s2.price AS sales_price,
       s2.sale_date AS sales_date
     FROM
      customer c
      INNER JOIN sales2 s2 ON c.id = s2.customer_id) AS sales
WHERE
    sales.sales_date >= '2001-04-01' AND sales.sales_date <= '2010-04-01'
    AND sales.sales_date >= '2001-04-01' AND sales.sales_date <= '2010-04-01'
GROUP BY
    sales.id;