时间戳

首页 > 解决方案 > 如何查找客户在给定日期范围内未进行销售的所有年份？

sql - 如何查找客户在给定日期范围内未进行销售的所有年份？

问题描述

我的数据库中有两个相关的表，假设它们是 Customer 和 Sale，如下所示：

客户
PK 客户ID 客户名称
客户
生日


和

销售
PK SaleId
FK CustomerId
SaleYear
SalePrice

我想在 SQL Developer 中创建一个 SELECT 语句，返回客户 a 没有进行销售的所有年份并显示所有这些年份。我试过这个：br>

SELECT CustomerName, SalePrice, LISTAGG(TRIM(SaleYear), ', ' WITHIN GROUP (ORDER BY SaleYear), '\d{4}+)(\,\s?\1){1,}', '\1') AS YEARS_WITHOUT_SALE
FROM CUSTOMER
LEFT JOIN SALE ON CUSTOMER.CustomerID = SALE.CustomerID
WHERE NOT EXSISTS (
    SELECT * FROM SALE
    WHERE CUSTOMER.CustomerID = SALE.CustomerId
    AND SaleYear >= 2015
    AND SaleYear <= 2020
);


我必须创建一个正则表达式来替换我得到的所有重复的年份值，所以忽略它。

我编写的代码似乎有效，但我从销售中获得了客户实际上没有销售的年份，例如 2016 年。但有些情况下客户在 2015 年到 2020 年范围内没有销售并且销售和客户之间没有关系，我想显示这个范围内的所有年份，比如 2015、2016、2017、2018、2019、2020。

还有另一种情况是客户在 2015 年和2020 年，但他在 2016 年进行了销售，因此显示的是 2016 年。显示它是因为有一个与该客户相关的销售，但在我的 BD 模型中，它在逻辑上被删除了。

但是在两个表之间没有关系的情况下，这意味着客户从未进行过销售，那么我必须显示给定范围内的所有年份，我认为它无法正常工作。

如果我的解释不够清楚，我很抱歉。我希望您能理解我的问题并给我一些提示以找到解决方案。

标签： sqloracle

解决方案

你需要一份销售清单。让我假设年份在sales. 然后使用 across join生成所有组合 - 以及一些机制来过滤掉存在的组合：

select c.customerid, y.salesyear
from customers c cross join
     (select distinct salesyear from sales) y left join
     sales s
     on s.salesyear = y.salesyear and s.customerid = c.customerid
where s.customerid is null;

如果您想要特定的年份范围，将这些年份包含在派生表中可能会更好：

select c.customerid, y.salesyear
from customers c cross join
     (select 2015 as salesyear from dual union all
      select 2016 as salesyear from dual union all
      select 2017 as salesyear from dual union all
      select 2018 as salesyear from dual union all
      select 2019 as salesyear from dual 
     ) y
     sales s
     on s.salesyear = y.salesyear and s.customerid = c.customerid
where s.customerid is null;

推荐阅读