求助按日历不间断日期

锄禾日当午

各位

做数据时需要将供应商交货日期、按不间断日历对应起来，这样的SQL该如何修改

1. select QU,CUST,Lot_No,SQty,SDate,分类,x.日期 from
   
2. ((select QU,CUST,Lot_No,UnfSetMH as SQty,SchSetMH as SDate,"HSet" as 分类 from [計画$a1:i]
   
3. union all
   
4. select QU,CUST,Lot_No,UnfSetMB as SQty,SchSetMB as SDate,"BSet" as 分类 from [計画$a1:i])a
   
5. left join [日歴$a1:a]x
   
6. on x.日期=a.SDate)
   
7. where SQty >0
   

复制代码

显示不间断日历.rar (38.25 KB, 下载次数: 11)

2019-6-4 16:32 上传

点击文件名下载附件

aaron918

1. select A.* from (select UnfSetMH as 数据,SchSetMH as 日期,"Hset" as 分类 from [計画$] union all
   
2. select UnfSetMB as 数据,SchSetMB as 日期,"Bset" as 分类 from [計画$])A right join [日歴$]B ON A.日期=B.日期

复制代码

2324

在你的基础上变动了一下，可以出来你的结果，但是会多出来一个空白列，不知道是什么原因select QU,CUST,Lot_No,SQty,SDate,分类,x.日期 from
((select QU,CUST,Lot_No,UnfSetMH as SQty,SchSetMH as SDate,"HSet" as 分类 from [計画$a1:i]  union all  select QU,CUST,Lot_No,UnfSetMB as SQty,SchSetMB as SDate,"BSet" as 分类 from [計画$a1:i])a  
left join [日歴$a1:a]x  on a.SDate=x.日期)
union
select QU,CUST,Lot_No,SQty,SDate,分类,x.日期 from
((select QU,CUST,Lot_No,UnfSetMH as SQty,SchSetMH as SDate,"HSet" as 分类 from [計画$a1:i]  union all  select QU,CUST,Lot_No,UnfSetMB as SQty,SchSetMB as SDate,"BSet" as 分类 from [計画$a1:i])a
right join [日歴$a1:a]x  on a.SDate=x.日期)

2324

2楼回答的又快又简洁，向你学习

锄禾日当午

aaron918 发表于 2019-6-5 11:03

效果做出了，但有以下几个问题，请赐教。
1）没办法再现出来，用你给的SQL语句，得出来的效果还是显示“不连续日期"
2)  就用W5：Z你做出的透视表效果，在【日历】工作表追加日期，比如追加”4/23   4/24   7/3“都显示不出来。