表变量/CTE的SQL帮助

Question

大师，我有一张桌子，上面记录了所有顾客的购物细节，如下所示。shopmode是主表id。

ShopDate    CustomerId  ShoppingMode
1/1/2011    a                  0
1/1/2011    a                  0
1/1/2011    a                  1
1/1/2011    b                  0
2/1/2011    a                 0
2/1/2011    b                 1
3/1/2011    a                 1
3/1/2011    b                 0
3/1/2011    c                0

我正试图提出对需求的质疑（日期为年/月/日）

1. 在shopdate、customerid、shopmode上为每位客户显示一条记录

1/1/2011  a  0
1/1/2011  a  1
1/1/2011  b  0

2. 在给定的日期范围内（2011年1月1日至2011年3月1日），需要最近的商店日期具有customerid+shopmode的唯一值

3/1/2011  a    1
3/1/2011  b    0
3/1/2011  c    0
2/1/2011  b    1
2/1/2011  a    0

3. 在给定的日期范围内（2011年1月1日至2011年3月1日），customerid的最新截止日期

3/1/2011   a   1    
3/1/2011   b   0    
3/1/2011   c   0

需要你的帮助。。

SELECT Max(shopdate),customerid, shopmode
FROM Table 

有了这个结果，我将加入shoppingdetail表来显示数据。创建一个表变量的字符串或CTE显示我可以与其他表连接。

Answer 1

1：仅按分组或选择不同的

with cte1 as (
select ShopDate, CustomerId, ShoppingMode
from table
group by  ShopDate, CustomerId, ShoppingMode)
select * from cte1;

2：首先，找到每天购物类型和顾客的数量。然后，对于那些只有一种购物类型的人，只需获得最长日期。

with cte2 as (
select ShopDate, CustomerId, max(ShoppingMode), 
      count(distinct ShoppingMode) as cnt
from table
where ShopDate between start_date and end_date
group by  ShopDate, CustomerId
)
select max(ShopDate)as ShopDate, CustomerId, ShoppingMode
from cte2
where cnt = 1
group by Customer_id;

3：只需选择所有客户，对其进行排名，然后选择您想要的：

with cust as (   
select 
     CustomerId, 
     row_number() over (partition by customerId order by ShopDate desc) as rnk
from table 
where ShopDate between start_date and end_date
)
select * from cust where rnk = 1

Answer 2

试试这个：

    SELECT *
  FROM (SELECT ShopDate,
               CustomerId,
               ShoppingMode,
               ROW_NUMBER ()
                  OVER (PARTITION BY ShopDate, CustomerId, ShoppingMode
                        ORDER BY ShopDate, CustomerId, ShoppingMode)
                  rn
          FROM yourtable where shopdate >= '01jan2011' AND shopdate <= '03jan2011')
 WHERE rn = 1;