如何找出客户购买的第一个产品项目，谁购买了特定的产品

Question

我想写一个查询来定位一组客户，他们购买了特定的2个产品类别，同时获得他们购买的第一个交易日期和第一个项目的信息。由于我使用了按功能分组，由于按功能分组的性质，我只能获得客户id，而不能获得第一件商品的购买。有什么想法可以解决这个问题吗？

我有事务表(t(、customer_id表(c(和产品表(p(。我的是SQL server 2008。

更新

SELECT t.customer_id
,t.product_category
,MIN(t.transaction_date) AS FIRST_TRANSACTION_DATE
,SUM(t.quantity) AS TOTAL_QTY
,SUM(t.sales) AS TOTAL_SALES
FROM transaction t
WHERE t.product_category IN ('VEGETABLES', 'FRUITS')
AND t.transaction_date BETWEEN '2020/01/01' AND '2022/09/30'
GROUP BY t.customer_id
HAVING COUNT(DISTINCT t.product_category) = 2

**Customer_id**  **transaction_date** **product_category**  **quantity** **sales**
1                   2022-05-30           VEGETABLES             1             100
1                   2022-08-30           VEGETABLES             1             100
2                   2022-07-30           VEGETABLES             1             100
2                   2022-07-30           FRUITS                 1             50
2                   2022-07-30           VEGETABLES             2             200
3                   2022-07-30           VEGETABLES             3             300
3                   2022-08-01           FRUITS                 1             50
3                   2022-08-05           FRUITS                 1             50
4                   2022-08-07           FRUITS                 1             50
4                   2022-09-05           FRUITS                 2             100

在上面，我想在执行SQL查询后显示的是

**Customer_id**  **FIRST_TRANSACTION_DATE** **first_product_category** **TOTAL_QUANTITY** **TOTAL_SALES**
2                   2022-07-30                VEGETABLES, FRUITS             4             350
3                   2022-07-30                VEGETABLES                    5             400

客户id 1和4将不会显示，因为他们只购买了蔬菜或水果，而不是同时购买

Answer 1

现在检查，BTW需要用product_category找到逻辑

select CustomerId, transaction_date, product_category, quantity, sales
from(
select CustomerId, transaction_date, product_category , sum(quantity) over(partition by CustomerId ) as quantity , sum(sales) over(partition by CustomerId ) as sales, row_number() over(partition by CustomerId order by transaction_date ASC) rn  
from(
select CustomerId, transaction_date, product_category, quantity, sales 
from tablee t
where  (product_category = 'FRUITS' and
EXISTS (select CustomerId
from tablee tt
where product_category = 'VEGETABLES'
and t.CustomerId = tt.CustomerId)) OR
(product_category = 'VEGETABLES' and
EXISTS (select CustomerId
from tablee tt
where product_category = 'FRUITS'
and t.CustomerId = tt.CustomerId)))x)over_all
where rn = 1;

这是FIDDLE