＋设为首页 ＋添加到收藏夹
表 total
innerid,date,outlet,total
表 sales
innerid,date,outlet,model,sales
表 total relation 表 sales as innerid
表 models
model
total和sales是一对多关系。
下面是找出缺少的model
Select
From total A Right Join
models B ON A.model=B.model
WHERE A.model IS NULL
下面都可以找出所有日期和商店的组合
select distinct date,outlet
from total
select distinc date,outlet
from sales
group by date,outlet
将缺少的model和全部的日期商店组合，做全排列，就是每天每家商店都要有这个model的纪录
也就是笛卡尔积
最后，还要有total表里的total ， 和 sales 表里的 sales
最终得到的记录集应该是
outlet,date,model,sales,total
1 2 3 44 100
1 2 4 0 100
1 2 5 12 100
.....
原有的model，把原有的sales,total带过来
新补充的model,把sales置为0 ， total用同一天，同一家商店的任意一个total(他们是相同的)
所有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) A1
left 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 (这个有没有关联，要不要你自已知道。)
下面是找出缺少的model
select model from models where model not in (select model from total)
create table total (innerid int ,date datetime,outlet int ,total int,model int)
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 23
create 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 23
create table models (model int)
21
22
23
24
select 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
