解决方案 »
- Web网页设计中调用数据库的问题
- 如何生成一个简易年(月)历?
- 对排产熟悉的进来聊聊!
- 关于ODBC的问题
- 用SQL语句怎么更改表名和字段名?谢谢!
- 求助IIS Virtual Directory Management for Sql Server 怎么配
- 在用SSIS是,在DataFlow里面转换一个表,在mapping column是提示错误:column "IP_No" can't convert between unicode and non_unicode s
- 高高手 help me!
- 后台用DB2,中间层用VC、IBM C,前端用VB,直接用WINSOCK通讯;关于应用程序的优化,是否有想法。
- 求个sql语句(根据一个表进行汇总加SUM函数)
- 求一段SQL语句
- 用不含"0"值相邻行,替换含"0"值行?
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