所有model:
select model from total
union
select model from models
---------------------------------
笛卡尔积:
select * from
(select model from total
union
select model from models) A,
(select distinc date,outlet from sales) B
-----------------------------------------select A1.*,C.total,D.sales(select * from
(select model from total
union
select model from models) A,
(select distinc date,outlet from sales) B) A1left join total C on A1.date=C.date and A1.outlet=C.outlet
left join sales D on A1.date=D.date and A1.outlet=D.outlet and A1.model=D.model
where C.innerid=D.innerid (这个有没有关联,要不要你自已知道。)
select model from total
union
select model from models
---------------------------------
笛卡尔积:
select * from
(select model from total
union
select model from models) A,
(select distinc date,outlet from sales) B
-----------------------------------------select A1.*,C.total,D.sales(select * from
(select model from total
union
select model from models) A,
(select distinc date,outlet from sales) B) A1left join total C on A1.date=C.date and A1.outlet=C.outlet
left join sales D on A1.date=D.date and A1.outlet=D.outlet and A1.model=D.model
where C.innerid=D.innerid (这个有没有关联,要不要你自已知道。)
where C.innerid=D.innerid (这个有没有关联,要不要你自已知道。)
select distinct date,outlet from total
from (select * from models,(select distinct date,outlet from total) tem1) tem2
left join sales tem3 on tem2.model=tem3.model and tem2.outlet=tem3.outlet and tem2.date=tem3.date
left join total tem4 on tem2.outlet=tem4.outlet and tem2.date=tem4.date
1 2003-04-04 00:00:00.000 10 30 21
2 2003-04-05 00:00:00.000 80 30 22
3 2003-04-06 00:00:00.000 70 1 23create table sales (innerid int ,date datetime,outlet int ,sales int,model int)
2 2003-04-05 00:00:00.000 80 30 22
3 2003-04-06 00:00:00.000 70 1 23create table models (model int)
21
22
23
24select e.model,e.date,e.outlet,isnull(d.sales,0) as sales ,isnull(e.total,0 ) outlet
from
(select * from
( select * from models ) a,
( select distinct date from sales union select distinct date from total ) b ,
(select distinct outlet from sales union select distinct outlet from total ) c ) e
left join sales d on e.model=d.model and e.date=d.date
left join total e on e.model=e.model and e.date=d.date
order by e.model,e.date,e.outlet
-----
outlet date model sales total
21 2003-04-04 00:00:00.000 10 0 0
21 2003-04-04 00:00:00.000 70 0 0
21 2003-04-04 00:00:00.000 80 0 0
21 2003-04-05 00:00:00.000 10 0 0
21 2003-04-05 00:00:00.000 70 0 0
21 2003-04-05 00:00:00.000 80 0 0
21 2003-04-06 00:00:00.000 10 0 0
21 2003-04-06 00:00:00.000 70 0 0
21 2003-04-06 00:00:00.000 80 0 0
22 2003-04-04 00:00:00.000 10 0 0
22 2003-04-04 00:00:00.000 70 0 0
22 2003-04-04 00:00:00.000 80 0 0
22 2003-04-05 00:00:00.000 10 30 30
22 2003-04-05 00:00:00.000 70 30 30
22 2003-04-05 00:00:00.000 80 30 30
22 2003-04-06 00:00:00.000 10 0 0
22 2003-04-06 00:00:00.000 70 0 0
22 2003-04-06 00:00:00.000 80 0 0
23 2003-04-04 00:00:00.000 10 0 0
23 2003-04-04 00:00:00.000 70 0 0
23 2003-04-04 00:00:00.000 80 0 0
23 2003-04-05 00:00:00.000 10 0 0
23 2003-04-05 00:00:00.000 70 0 0
23 2003-04-05 00:00:00.000 80 0 0
23 2003-04-06 00:00:00.000 10 1 1
23 2003-04-06 00:00:00.000 70 1 1
23 2003-04-06 00:00:00.000 80 1 1
24 2003-04-04 00:00:00.000 10 0 0
24 2003-04-04 00:00:00.000 70 0 0
24 2003-04-04 00:00:00.000 80 0 0
24 2003-04-05 00:00:00.000 10 0 0
24 2003-04-05 00:00:00.000 70 0 0
24 2003-04-05 00:00:00.000 80 0 0
24 2003-04-06 00:00:00.000 10 0 0
24 2003-04-06 00:00:00.000 70 0 0
24 2003-04-06 00:00:00.000 80 0 0