oracle 查找不同日期的相同项目的采购

cgfeq70w 于 2022-11-03 发布在 Oracle

关注(0)|答案(2)|浏览(128)

我尝试寻找在不同日期购买相同项目超过一次的客户。我让它部分运作。我无法取得客户的名字/姓氏和item_name，除非将它加入group by子句。此外，我想要包含在不同日期购买相同uten的次数计数。
我怀疑group by可能不是最好的解决方案。使用self JOIN或lead会更好地解决这个问题吗？

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Abby', 'Katz' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME) AS
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL;

CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-11 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2,101,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3,102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL;

With CTE as (
 SELECT customer_id
                ,product_id 
                ,trunc(purchase_date)
  FROM purchases
  GROUP BY customer_id 
                    ,product_id
                   ,trunc(purchase_date)
)
SELECT  customer_id, product_id
FROM CTE
GROUP BY customer_id                  ,product_id 
HAVING COUNT(1)>1

oracle

来源：https://stackoverflow.com/questions/74235030/find-purchase-if-same-item-on-different-days

2条答案

按热度按时间

js81xvg61#

这里我将使用exists逻辑：

SELECT DISTINCT c.first_name, c.last_name
FROM customers c
INNER JOIN purchases p
    ON p.customer_id = c.customer_id
WHERE EXISTS (
    SELECT 1
    FROM purchases p2
    WHERE p2.customer_id = p.customer_id AND
          p2.product_id = p.product_id   AND
          TRUNC(p2.purchase_date) <> TRUNC(p.purchase_date)
);

简单地英语，上面的查询要求查找在不同日期购买相同产品的所有客户。

赞(0）回复(0）举报 2022-11-03

8aqjt8rx2#

这可能是一个选项：使用count函数的解析形式和读取计数大于1的行;根据您发布的数据，是丽莎在两个不同的日期购买了棕色鞋子。

SQL> WITH
  2     temp
  3     AS
  4        (  SELECT c.first_name,
  5                  i.product_name,
  6                  TRUNC (p.purchase_date),
  7                  COUNT (*) OVER (PARTITION BY c.first_name, i.product_name) cnt
  8             FROM purchases p
  9                  JOIN customers c ON c.customer_id = p.customer_id
 10                  JOIN items i ON i.product_id = p.product_id
 11         GROUP BY c.first_name, i.product_name, TRUNC (p.purchase_date))
 12  SELECT DISTINCT first_name, product_name, cnt
 13    FROM temp
 14   WHERE cnt > 1;

FIRST PRODUCT_NAM        CNT
----- ----------- ----------
Lisa  Brown Shoes          2

SQL>

赞(0）回复(0）举报 2022-11-03

我来回答

oracle 查找不同日期的相同项目的采购

2条答案

相关问题

热门标签

最新问答