CREATE function addsale02(@store varchar(7999),@style varchar(7999),@datebeg char(10),@dateend char(10))
Returns @MultiTable Table (store varchar(50),
stname varchar(50),
qty int,
amount numeric(16,4),
famount numeric(16,4),
lqty int,
lamount numeric(16,4),
lfamount numeric(16,4)
)begin
declare @lastyear table(
lstore varchar(50),
lqty int,
lamount numeric(16,4),
lfamount numeric(16,4))insert into @lastyear(lstore,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount)
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f
where a.store=v.store and a.masterid=b.masterid
and billdate between (dateadd(yy,-1,@datebeg)) and (dateadd(yy,-1,@dateend))
and a.charged=1 and a.store = f.store
group by a.store
insert into @MultiTable (store,qty,amount,famount,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount),z.lqty,z.lamount,z.lfamount
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f,@lastyear z
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store and a.store=z.lstore
group by a.store,z.lqty,z.lamount,z.lfamountupdate t set
t.stname=d.stname
from @multitable t,Tdefstore d
WHERE (t.store= d.store)
Return
end--;
这个函数是算今年的销售@multitabel和同期去年@lastyear的销售其中
其中今年有开业的部分店铺去年没有开业,就没有销售
用以下连接的时候,就会自动删除去年的没有营业的店铺
insert into @MultiTable (store,qty,amount,famount,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount),z.lqty,z.lamount,z.lfamount
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f,@lastyear z
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store and a.store=z.lstore
group by a.store,z.lqty,z.lamount,z.lfamount如何修改以上语句呢?在store列中才会显示没有开的店铺呢?lqty,lamount,lfamount为0呢?以上语句如何改效率还能再高些呢?谢谢各位了!
Returns @MultiTable Table (store varchar(50),
stname varchar(50),
qty int,
amount numeric(16,4),
famount numeric(16,4),
lqty int,
lamount numeric(16,4),
lfamount numeric(16,4)
)begin
declare @lastyear table(
lstore varchar(50),
lqty int,
lamount numeric(16,4),
lfamount numeric(16,4))insert into @lastyear(lstore,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount)
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f
where a.store=v.store and a.masterid=b.masterid
and billdate between (dateadd(yy,-1,@datebeg)) and (dateadd(yy,-1,@dateend))
and a.charged=1 and a.store = f.store
group by a.store
insert into @MultiTable (store,qty,amount,famount,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount),z.lqty,z.lamount,z.lfamount
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f,@lastyear z
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store and a.store=z.lstore
group by a.store,z.lqty,z.lamount,z.lfamountupdate t set
t.stname=d.stname
from @multitable t,Tdefstore d
WHERE (t.store= d.store)
Return
end--;
这个函数是算今年的销售@multitabel和同期去年@lastyear的销售其中
其中今年有开业的部分店铺去年没有开业,就没有销售
用以下连接的时候,就会自动删除去年的没有营业的店铺
insert into @MultiTable (store,qty,amount,famount,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount),z.lqty,z.lamount,z.lfamount
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f,@lastyear z
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store and a.store=z.lstore
group by a.store,z.lqty,z.lamount,z.lfamount如何修改以上语句呢?在store列中才会显示没有开的店铺呢?lqty,lamount,lfamount为0呢?以上语句如何改效率还能再高些呢?谢谢各位了!
提高效率方面应该有帮助
http://blog.csdn.net/daiyueqiang/archive/2009/11/04/4765716.aspx
tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f,@lastyear z
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store and a.store=z.lstore 类似于 inner join楼主可以使用
tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f
left join @lastyear z on a.store=z.lstore
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store 这样结构试试看
使用left join
on 条件
insert into @MultiTable (store,qty,amount,famount,lqty,lamount,lfamount)
select a.store,sum(b.qty),sum(b.amount),sum(b.famount),z.lqty,z.lamount,z.lfamount
from tbussale a,tbussaledt b,fgetstore(@store) v, tdefstore f
left join @lastyear z on a.store=z.lstore
where a.store=v.store and a.masterid=b.masterid
and billdate between @datebeg and @dateend
and a.charged=1 and a.store = f.store 服务器: 消息 107,级别 16,状态 2,过程 addsale02,行 47
列前缀 'a' 与查询中所用的表名或别名不匹配。
怎么改啊!