解决了 create table #tmpMonth ( f_Month nvarchar(50) ) CREATE TABLE #tmp1( [F_ShopID] [nvarchar](50) NOT NULL, [F_Month] [nvarchar](50) NOT NULL, [F_FatherSupplierId] [nvarchar](50) NOT NULL, [F_SupplierId] [nvarchar](50) NOT NULL ) insert #tmpMonth values('201501') insert #tmpMonth values('201502') insert #tmpMonth values('201503') insert #tmpMonth values('201504') insert #tmpMonth values('201505') insert #tmpMonth values('201506') insert #tmpMonth values('201507') insert #tmpMonth values('201508')insert #tmp1 values('0001','201501','0028','002801') insert #tmp1 values('0001','201503','0028','002804') insert #tmp1 values('0001','201504','0028','002804') insert #tmp1 values('0001','201506','0028','002803')select t1.F_month ,t2.F_ShopID ,t2.F_FatherSupplierID ,t2.F_SupplierID from #tmpMonth t1 left join #tmp1 t2 on t2.f_month = (select max(f_month) from #tmp1 t3 where t3.f_month <= t1.f_month)drop table #tmpMonth drop table #tmp1
加强版 create table #tmpMonth ( f_Month nvarchar(50) ) CREATE TABLE #tmp1( [F_ShopID] [nvarchar](50) NOT NULL, [F_Month] [nvarchar](50) NOT NULL, [F_FatherSupplierId] [nvarchar](50) NOT NULL, [F_SupplierId] [nvarchar](50) NOT NULL ) insert #tmpMonth values('201501') insert #tmpMonth values('201502') insert #tmpMonth values('201503') insert #tmpMonth values('201504') insert #tmpMonth values('201505') insert #tmpMonth values('201506') insert #tmpMonth values('201507') insert #tmpMonth values('201508')insert #tmp1 values('0001','201501','0028','002801') insert #tmp1 values('0001','201503','0028','002804') insert #tmp1 values('0001','201504','0028','002804') insert #tmp1 values('0001','201506','0028','002803')insert #tmp1 values('0002','201501','0028','002801') insert #tmp1 values('0002','201503','0028','002803') insert #tmp1 values('0002','201505','0028','002805') insert #tmp1 values('0002','201507','0028','002807')select t1.F_month ,t2.F_ShopID ,t2.F_FatherSupplierID ,t2.F_SupplierID from #tmpMonth t1 left join #tmp1 t2 on t2.f_month = (select max(f_month) from #tmp1 t3 where t3.f_month <= t1.f_month and t3.F_ShopID = t2.F_ShopID and t3.F_FatherSupplierID = t2.F_FatherSupplierID ) order by t1.F_month ,t2.F_ShopID ,t2.F_FatherSupplierID ,t2.F_SupplierID drop table #tmpMonth drop table #tmp1
假设年月表为t1,数据表为t2:SELECT t3.F_month , t2.F_Shopid , t2.F_SupplierID , t2.F_FatherSupplierID FROM t2 JOIN ( SELECT t1.F_month , MAX(t2.F_month) AS F_month_actual FROM t1, t2 WHERE t1.F_month >= t2.F_month GROUP BY t1.F_month ) t3 ON t2.F_month = t3.F_month_actual
写一个差不多的: with cte1 as ( select '201501' F_Month union all select '201502' F_Month union all select '201503' F_Month union all select '201504' F_Month union all select '201505' F_Month ), cte2 as ( select '201501' F_Month, '001' F_Shopid, '003001' F_SupplierID, '0030' F_FatherSupplierID union all select '201503' F_Month, '001' F_Shopid, '003002' F_SupplierID, '0030' F_FatherSupplierID ) select aa.F_Month, bb.F_Shopid, bb.F_SupplierID, bb.F_FatherSupplierID from (select cte1.F_Month, MAX(cte2.F_Month) F_MonthMax from cte1 left join cte2 on cte1.F_Month>=cte2.F_Month group by cte1.F_Month) aa left join cte2 bb on aa.F_MonthMax=bb.F_Month
create table #tmpMonth
(
f_Month nvarchar(50)
)
CREATE TABLE #tmp1(
[F_ShopID] [nvarchar](50) NOT NULL,
[F_Month] [nvarchar](50) NOT NULL,
[F_FatherSupplierId] [nvarchar](50) NOT NULL,
[F_SupplierId] [nvarchar](50) NOT NULL
)
insert #tmpMonth values('201501')
insert #tmpMonth values('201502')
insert #tmpMonth values('201503')
insert #tmpMonth values('201504')
insert #tmpMonth values('201505')
insert #tmpMonth values('201506')
insert #tmpMonth values('201507')
insert #tmpMonth values('201508')insert #tmp1 values('0001','201501','0028','002801')
insert #tmp1 values('0001','201503','0028','002804')
insert #tmp1 values('0001','201504','0028','002804')
insert #tmp1 values('0001','201506','0028','002803')select
t1.F_month
,t2.F_ShopID
,t2.F_FatherSupplierID
,t2.F_SupplierID
from #tmpMonth t1
left join #tmp1 t2
on t2.f_month = (select max(f_month) from #tmp1 t3 where t3.f_month <= t1.f_month)drop table #tmpMonth
drop table #tmp1
create table #tmpMonth
(
f_Month nvarchar(50)
)
CREATE TABLE #tmp1(
[F_ShopID] [nvarchar](50) NOT NULL,
[F_Month] [nvarchar](50) NOT NULL,
[F_FatherSupplierId] [nvarchar](50) NOT NULL,
[F_SupplierId] [nvarchar](50) NOT NULL
)
insert #tmpMonth values('201501')
insert #tmpMonth values('201502')
insert #tmpMonth values('201503')
insert #tmpMonth values('201504')
insert #tmpMonth values('201505')
insert #tmpMonth values('201506')
insert #tmpMonth values('201507')
insert #tmpMonth values('201508')insert #tmp1 values('0001','201501','0028','002801')
insert #tmp1 values('0001','201503','0028','002804')
insert #tmp1 values('0001','201504','0028','002804')
insert #tmp1 values('0001','201506','0028','002803')insert #tmp1 values('0002','201501','0028','002801')
insert #tmp1 values('0002','201503','0028','002803')
insert #tmp1 values('0002','201505','0028','002805')
insert #tmp1 values('0002','201507','0028','002807')select
t1.F_month
,t2.F_ShopID
,t2.F_FatherSupplierID
,t2.F_SupplierID
from #tmpMonth t1
left join #tmp1 t2
on t2.f_month = (select max(f_month) from #tmp1 t3 where t3.f_month <= t1.f_month
and t3.F_ShopID = t2.F_ShopID and t3.F_FatherSupplierID = t2.F_FatherSupplierID
)
order by
t1.F_month
,t2.F_ShopID
,t2.F_FatherSupplierID
,t2.F_SupplierID
drop table #tmpMonth
drop table #tmp1
t3.F_month
, t2.F_Shopid
, t2.F_SupplierID
, t2.F_FatherSupplierID
FROM t2
JOIN (
SELECT
t1.F_month
, MAX(t2.F_month) AS F_month_actual
FROM t1, t2
WHERE t1.F_month >= t2.F_month
GROUP BY t1.F_month
) t3 ON t2.F_month = t3.F_month_actual
with
cte1 as
(
select '201501' F_Month union all
select '201502' F_Month union all
select '201503' F_Month union all
select '201504' F_Month union all
select '201505' F_Month
),
cte2 as
(
select '201501' F_Month, '001' F_Shopid, '003001' F_SupplierID, '0030' F_FatherSupplierID union all
select '201503' F_Month, '001' F_Shopid, '003002' F_SupplierID, '0030' F_FatherSupplierID
)
select aa.F_Month, bb.F_Shopid, bb.F_SupplierID, bb.F_FatherSupplierID from
(select cte1.F_Month, MAX(cte2.F_Month) F_MonthMax from cte1 left join cte2 on cte1.F_Month>=cte2.F_Month group by cte1.F_Month) aa
left join cte2 bb on aa.F_MonthMax=bb.F_Month