none
打扰!请教个sql,可以根据年月显示数据.但如果有的月份没有数据则显示上月数据打扰。 RRS feed

  • 问题

  • 具体内容如下,由于数据比较度多,希望用sql能出来,如果使用游标等,可能会很慢。十分感谢!

    有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


    2015年3月7日 9:27

答案

  • 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

    2015年3月8日 15:24