求SQL，高手进来试一试！
------预测单create table #yc(cp varchar(10),startdate datetime,enddate datetime ,qty int)insert into #yc select 'A','2012-05-01','2012-05-07',100union select 'B','2012-05-02','2012-05-05',200union select 'C','2012-05-01','2012-05-10',300union select 'D','2012-05-01','2012-05-08',400union select 'E','2012-05-01','2012-05-09',500-----发货单create table #fh(cp varchar(10),fhdate datetime ,qty int)insert into #fhselect 'B','2012-05-04',50unionselect 'A','2012-05-08',30unionselect 'C','2012-05-10',100unionselect 'D','2012-05-12',200unionselect 'F','2012-05-15',100---------得到下面结果。说明：发货单发货日期在预测单时间范围内，就并成一行，不成，就单独成一行。cp startdate enddate fhdate ycqty fhqty---------- ----------------------- ----------------------- ----------------------- ----------- -----------A NULL NULL 2012-05-08 00:00:00.000 NULL 30A 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 NULL 100 NULLB 2012-05-02 00:00:00.000 2012-05-05 00:00:00.000 2012-05-04 00:00:00.000 100 50C 2012-05-01 00:00:00.000 2012-05-10 00:00:00.000 2012-05-10 00:00:00.000 300 100D NULL NULL 2012-05-12 00:00:00.000 NULL 200D 2012-05-01 00:00:00.000 2012-05-08 00:00:00.000 NULL 400 NULLE 2012-05-01 00:00:00.000 2012-05-09 00:00:00.000 NULL 500 NULLF NULL NULL 2012-05-15 00:00:00.000 NULL 100
select cp, null as startdate , null as enddate ,fhdate ,null as ycqty,fhqty from #fhunion allselect cp,startdate , enddate ,null as fhdate ,ycqty,null as fhqty from #fhorder by cp
[解决办法]
木有看懂意思。
[解决办法]
full join ?
[解决办法]
select cp=isnull(s.cp,t.cp),startdate,enddate,fhdate,ycqty=s.qty,t.qty from #yc s full join #fh t on s.cp=t.cp and fhdate between startdate and enddateorder by isnull(s.cp,t.cp)A 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 NULL 100 NULLA NULL NULL 2012-05-08 00:00:00.000 NULL 30B 2012-05-02 00:00:00.000 2012-05-05 00:00:00.000 2012-05-04 00:00:00.000 200 50C 2012-05-01 00:00:00.000 2012-05-10 00:00:00.000 2012-05-10 00:00:00.000 300 100D 2012-05-01 00:00:00.000 2012-05-08 00:00:00.000 NULL 400 NULLD NULL NULL 2012-05-12 00:00:00.000 NULL 200E 2012-05-01 00:00:00.000 2012-05-09 00:00:00.000 NULL 500 NULLF NULL NULL 2012-05-15 00:00:00.000 NULL 100
[解决办法]
select a.cp,a.startdate,a.enddate,fhdate=(select fhdate from #fh where a.cp=cp and fhdate>=a.startdate and fhdate<=a.enddate),a.qty as ycqty,fhqty=(select qty from #fh where a.cp=cp and fhdate>=a.startdate and fhdate<=a.enddate) from #yc a union select a.cp,startdate=(select startdate from #yc where a.cp=cp and a.fhdate>=startdate and a.fhdate<=enddate),enddate=(select enddate from #yc where a.cp=cp and a.fhdate>=startdate and a.fhdate<=enddate),fhdate,ycqty=(select qty from #yc where a.cp=cp and a.fhdate>=startdate and a.fhdate<=enddate),a.qty as fhqty from #fh a
[解决办法]
select *from (select cp,max(startdate) startdate,max(enddate) enddate,max(fhdate) fhdate,max(ycqty) ycqty,max(fhqty) fhqtyfrom (select cp,startdate,enddate,null as fhdate,qty as ycqty,null as fhqty from #yc union select cp,null,null,fhdate,null,qty from #fh ) tgroup by cp ) awhere fhdate between startdate and enddateunionselect cp,startdate,enddate,fhdate,ycqty,fhqtyfrom (select cp,startdate,enddate,null as fhdate,qty as ycqty,null as fhqty from #yc union select cp,null,null,fhdate,null,qty from #fh ) t where cp not in ( select cp from ( select cp,max(startdate) startdate,max(enddate) enddate,max(fhdate) fhdate,max(ycqty) ycqty,max(fhqty) fhqty from (select cp,startdate,enddate,null as fhdate,qty as ycqty,null as fhqty from #yc union select cp,null,null,fhdate,null,qty from #fh ) t group by cp ) a where fhdate between startdate and enddate)
[解决办法]
create table #yc(cp varchar(10),startdate datetime,enddate datetime ,qty int)insert into #yc select 'A','2012-05-01','2012-05-07',100union select 'B','2012-05-02','2012-05-05',200union select 'C','2012-05-01','2012-05-10',300union select 'D','2012-05-01','2012-05-08',400union select 'E','2012-05-01','2012-05-09',500-----发货单create table #fh(cp varchar(10),fhdate datetime ,qty int)insert into #fhselect 'B','2012-05-04',50unionselect 'A','2012-05-08',30unionselect 'C','2012-05-10',100unionselect 'D','2012-05-12',200unionselect 'F','2012-05-15',100select isnull(a.cp,b.cp) as cp,a.startdate,a.enddate,b.fhdate,a.qty,b.qty from #yc a full join #fh b on a.cp=b.cp and b.fhdate between a.startdate and a.enddateorder by cp,startdate/*cp startdate enddate fhdate qty qtyA NULL NULL 2012-05-08 00:00:00.000 NULL 30A 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 NULL 100 NULLB 2012-05-02 00:00:00.000 2012-05-05 00:00:00.000 2012-05-04 00:00:00.000 200 50C 2012-05-01 00:00:00.000 2012-05-10 00:00:00.000 2012-05-10 00:00:00.000 300 100D NULL NULL 2012-05-12 00:00:00.000 NULL 200D 2012-05-01 00:00:00.000 2012-05-08 00:00:00.000 NULL 400 NULLE 2012-05-01 00:00:00.000 2012-05-09 00:00:00.000 NULL 500 NULLF NULL NULL 2012-05-15 00:00:00.000 NULL 10*/不要那么复杂吧
[解决办法]
create table #yc(cp varchar(10),startdate datetime,enddate datetime ,qty int)insert into #yc select 'A','2012-05-01','2012-05-07',100union select 'B','2012-05-02','2012-05-05',200union select 'C','2012-05-01','2012-05-10',300union select 'D','2012-05-01','2012-05-08',400union select 'E','2012-05-01','2012-05-09',500-----发货单create table #fh(cp varchar(10),fhdate datetime ,qty int)insert into #fhselect 'B','2012-05-04',50unionselect 'A','2012-05-08',30unionselect 'C','2012-05-10',100unionselect 'D','2012-05-12',200unionselect 'F','2012-05-15',100select isnull(a.cp, b.cp) as cp, startdate, enddate, fhdate , a.qty as ycqty, b.qty as fhqtyfrom #yc a full join #fh b on a.cp = b.cp and b.fhdate between a.startdate and a.enddateorder by cp, a.startdate, b.fhdatedrop table #yc, #fh-- cp startdate enddate fhdate ycqty fhqty-- A 2012-05-08 00:00:00.000 30-- A 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 100 -- B 2012-05-02 00:00:00.000 2012-05-05 00:00:00.000 2012-05-04 00:00:00.000 200 50-- C 2012-05-01 00:00:00.000 2012-05-10 00:00:00.000 2012-05-10 00:00:00.000 300 100-- D 2012-05-12 00:00:00.000 200-- D 2012-05-01 00:00:00.000 2012-05-08 00:00:00.000 400 -- E 2012-05-01 00:00:00.000 2012-05-09 00:00:00.000 500 -- F 2012-05-15 00:00:00.000 100
