列出每年購買過的（忠實）客戶-有解無憂

我想從列中列出從每個明顯可用的年份進行購買的 customer_id。

該列位于 sales.orders 中，稱為“order_date”

這是我到目前為止所做的（沒有作業）：

--List customers who've made purchases in every year
SELECT customer_id
FROM sales.orders
WHERE year(order_date) = ALL (
    select distinct YEAR(order_date)
    from sales.orders
)

我想要的東西只有在他們自開業以來每年至少購買一次時才顯示 customer_id。

uj5u.com熱心網友回復：

使用COUNT，@Lanru 的建議沒有錯。

在這里你可以做什么

假設您的表格如下所示：

CREATE TABLE sales.orders 
( 
    customer_id NVARCHAR(4000), 
    order_date date
); 

INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1002', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1002', '10-10-2022'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2021'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2021'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1001', '10-10-2022'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2020'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2021'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2022'); 
INSERT INTO sales.orders (customer_id, order_date) VALUES ('C1003', '10-10-2022');

這是您要求的解決方案：

    SELECT me.customer_id FROM (
SELECT  DISTINCT customer_id, 
        COUNT(DISTINCT YEAR(order_date)) AS 'count_year'
FROM sales.orders
GROUP BY customer_id ) AS me
WHERE me.count_year = (SELECT COUNT(DISTINCT YEAR(order_date) ) FROM sales.orders)

uj5u.com熱心網友回復：

SELECT *
FROM sales.customers
WHERE customer_id IN (
        SELECT t.customer_id
        FROM (
            SELECT o.customer_id,
                count(DISTINCT (year(order_date))) AS cnt
            FROM sales.orders AS o
            GROUP BY customer_id
            ) t
        WHERE t.cnt = (
                SELECT count(DISTINCT (year(order_date)))
                FROM sales.orders
                )
        )

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/528846.html

標籤：sqltsql

上一篇：帶有連接節點的鏈接的聚集氣泡

下一篇：使用存盤程序的多重搜索過濾器