请较个sql,可以根据年月显示数据.但如果有的月份没有数据则显示上月数据

[LGame]

有2张表
表1 :存放的年月信息
F_Month
201501
201502
201503
201504
201505

表2存放的各个年月的对应数据,但是不一定每个月都有数据
F_month F_Shopid F_SupplierID F_FatherSupplierID
201501 001 003001 0030
201503 001 003002 0030

想要的结果数据是
F_month F_Shopid F_SupplierID F_FatherSupplierID
201501 001 003001 0030
201502 001 003001 0030
201503 001 003002 0030
201504 001 003002 0030
201505 001 003002 0030

[LGame]

解决了

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

[还在加载中灬]

子查询的关联方式可能不怎么好

[LGame]

加强版 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

[oraclecaicai]

假设年月表为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

[zbdzjx]

写一个差不多的：

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