表A
ID
Code(商店代码)
Sales(销售)(每天每店一个销售额)
Time(时间)表B
Code(商店代码)
YG(预估销售多少)(每年每月每店只有一个预估)
Time(时间)生成这样的视图(统计出店每年每月的销售总和,预估)
Code(商店代码)
Count(销售总和)
YG(预估)
Time(时间)(年月)
ID
Code(商店代码)
Sales(销售)(每天每店一个销售额)
Time(时间)表B
Code(商店代码)
YG(预估销售多少)(每年每月每店只有一个预估)
Time(时间)生成这样的视图(统计出店每年每月的销售总和,预估)
Code(商店代码)
Count(销售总和)
YG(预估)
Time(时间)(年月)
Code(商店代码)
Count(销售总和)
YG(预估)
Time(时间)(年月)----------------------------------------------------------------------------
是啥样的格式哦 ?
(select sum(yg), year(Time) y, month(Time) m from 表b
group by year(Time), month(Time)) a inner join (select sum(sales), year(Time) y , month(Time) m from 表a
group by year(Time), month(Time)) bon a.y=b.y and a.m=b.m
(Code,Count,YG,Time)
as
(select a.Code as Code,a.Sales as Sales,b.YG as YG a.Sales
from a,b where a.Code=b.Code
)
这样????
ID Code Sales Time
1 a 1000 2006.10.1
2 a 1000 2006.10.2
3 b 1000 2006.10.1
4 b 1000 2006.10.2
表B
Code YG Time
a 5000 2006.10(月份)
b 3000 2006.10视图
Code CountSales YG Time
a 2000 5000 2006.10(月份)
b 2000 3000 2006.10
a.Code ,sum(a.Sale),convert(varchar(6),Time,120)
from 表A a
group by
a.Code ,
convert(varchar(6),Time,120)select T.Code,T.Count,b.YG,T.Time
from
(
select
a.Code ,Count = sum(a.Sales),Time = convert(varchar(6),Time,120)
from 表A a
group by
a.Code ,
convert(varchar(6),Time,120)
) T
join 表B b
on T.Code = b.Code
from a,b
where a.code=b.code
group by code,yg,convert(varchar(7),time,111 ) 试试吧
(Code,Count,YG,Time)
as
select T.Code,T.Count,b.YG,T.Time
from
(
select
a.Code ,Count = sum(a.Sales),Time = convert(varchar(6),Time,120)
from 表A a
group by
a.Code ,
convert(varchar(6),Time,120)
) T
join 表B b
on T.Code = b.Code
表A a inner join
表B b on a.Code=b.Code
and year(a.Time)+'.'+month(a.Time) = b.Time
Group by a.Code,Time
(select code,sum(sales) x from a group by code)aa,
(select code,yg,time from b)bb where aa.code=bb.code