查询上次销售日期后1年内转售的商品

您可以通过查找最大值并根据您的条件加入来实现这一点。

declare @TableA table (Car_Type varchar(64)
                      ,Car_Reg_No int
                      ,Sold_Date datetime
                      ,Listing_No varchar(6))

insert into @TableA
values
insert into @TableA
values
('Hatch',23789,'2017-02-03 11:26','X6529'),
('Coupe',16723,'2017-11-07 09:40','N8156'),
('Sedan',35216,'2017-05-23 10:34','M8164'),
('Hatch',23789,'2016-09-16 04:30','O7361'),
('Coupe',16723,'2014-11-07 09:40','N8156')

;with cte as(
select
    Car_Type
    ,Car_Reg_No
    ,Latest_Sold_Date = max(Sold_Date)
from
    @TableA
group by 
    Car_Type
    ,Car_Reg_No)

select 
    a.Car_Type
    ,a.Car_Reg_No
    ,Sold_Count = count(b.Listing_No) + 1
    ,a.Latest_Sold_Date
from cte a
    inner join
    @TableA b on 
    b.Car_Reg_No = a.Car_Reg_No
    and b.Sold_Date != a.Latest_Sold_Date
    and datediff(day,b.Sold_Date,a.Latest_Sold_Date) < 366
    --if you want only cars which were sold within last year too, uncomment this
    --and datediff(day,a.Latest_Sold_Date,getdate()) < 366
group by
    a.Car_Type
    ,a.Car_Reg_No
    ,a.Latest_Sold_Date

据我所知。。,

select sd1.Car_Type, sd1.Car_Reg_No,
count(sd1.Car_Reg_No) + 1 'no of sales in last one year', --1 is added because, see the last condition
sd1.Sold_Date 'Last sold date'
from(
select *,ROW_NUMBER() over(partition by Car_Reg_No order by sold_date desc) as rn from #Table) as sd1
join 
(select * from #Table) as sd2
on sd1.Car_Type = sd2.Car_Type 
and DATEDIFF(dd,sd2.Sold_Date,sd1.Sold_Date) < 366
and sd1.rn = 1 
and sd1.Sold_Date <> sd2.Sold_Date -- here last sold is eliminated. so count is added by one.
group by sd1.Car_Type,sd1.Sold_Date, sd1.Car_Reg_No
order by sd1.Car_Reg_No