select
产品编码,名称,
sum(出货总数) as出货总数,
sum(返货总数) as 返货总数,
sum(正价出货数) as 正价出货数,
sum(正价结算额) as 正价结算额,
sum(特价出货数) as 特价出货数,
sum(特价结算额) as 特价结算额,
sum(合计单品结算额) as 合计单品结算额,
sum(返货总数)/(sum(出货总数)-sum(返货总数)) as 单品返货率
from (
select
b.cpbh as 产品编码,
b.name as 名称,
b.isum as 出货总数,
0 as 返货总数,
case when tjflag=0 then b.isum else 0 end as 正价出货数,
case when tjflag=0 then b.isum*b.jg_z*(1-a.x)*(1-a.y)*(1-a.z) else 0 end as 正价结算额,
case when tjflag<>0 then b.isum else 0 end as 特价出货数,
case when tjflag<>0 then b.isum*b.jg_z*(1-b.tj_x)*(1-b.tj_y)*(1-b.tj_z) else 0 end as 特价结算额,
case when tjflag=0 then b.isum*b.jg_z*(1-a.x)*(1-a.y)*(1-a.z) else b.isum*b.jg_z*(1-b.tj_x)*(1-b.tj_y)*(1-b.tj_z) end as 合计单品结算额
from mx_yywd a,chd_cpmx b
where a,wdid=b.wdid
union all
select
b.cpbh as 产品编码,
b.name as 名称,
0 as 出货总数,
b.isum as 返货总数,
0 as 正价出货数,
0 as 正价结算额,
0 as 特价出货数,
0 as 特价结算额,
0 as 合计单品结算额
from mx_yywd a,fhd_cpmx b
where a,wdid=b.wdid
) as x
group by 产品编码,名称
产品编码,名称,
sum(出货总数) as出货总数,
sum(返货总数) as 返货总数,
sum(正价出货数) as 正价出货数,
sum(正价结算额) as 正价结算额,
sum(特价出货数) as 特价出货数,
sum(特价结算额) as 特价结算额,
sum(合计单品结算额) as 合计单品结算额,
sum(返货总数)/(sum(出货总数)-sum(返货总数)) as 单品返货率
from (
select
b.cpbh as 产品编码,
b.name as 名称,
b.isum as 出货总数,
0 as 返货总数,
case when tjflag=0 then b.isum else 0 end as 正价出货数,
case when tjflag=0 then b.isum*b.jg_z*(1-a.x)*(1-a.y)*(1-a.z) else 0 end as 正价结算额,
case when tjflag<>0 then b.isum else 0 end as 特价出货数,
case when tjflag<>0 then b.isum*b.jg_z*(1-b.tj_x)*(1-b.tj_y)*(1-b.tj_z) else 0 end as 特价结算额,
case when tjflag=0 then b.isum*b.jg_z*(1-a.x)*(1-a.y)*(1-a.z) else b.isum*b.jg_z*(1-b.tj_x)*(1-b.tj_y)*(1-b.tj_z) end as 合计单品结算额
from mx_yywd a,chd_cpmx b
where a,wdid=b.wdid
union all
select
b.cpbh as 产品编码,
b.name as 名称,
0 as 出货总数,
b.isum as 返货总数,
0 as 正价出货数,
0 as 正价结算额,
0 as 特价出货数,
0 as 特价结算额,
0 as 合计单品结算额
from mx_yywd a,fhd_cpmx b
where a,wdid=b.wdid
) as x
group by 产品编码,名称
,name=isnull(a.name,b.name)
,chs=isnull(a.chs,0),zjxss=isnull(a.zjxss,0)
,fhs=isnull(a.fhs,0),zjjse=isnull(a.zjjse,0)
from(
select cpbh,name,sum(isum) as chs,sum(isum*jg_zj) as zjxss
from chd_cpmx
group by cpbh,name
)a full join(
select cpbh,name,sum(isum) as fhs,sum(isum*jg_zj) as zjjse
from chd_cpmx
group by cpbh,name
)b on a.cpbh=b.cpbh and a.name=b.name
,正价出货数,正价结算额=正价结算额*(1-x)*(1-Y)*(1-Z)
,特价出货数,特价结算额=特价结算额*(1-tj_x)*(1-tj_y)*(1-tj_z)--不知道你这里的tj_x,tj_y,tj_z是那里来的?
,合计单品结算额=正价结算额*(1-x)*(1-Y)*(1-Z)
+特价结算额*(1-tj_x)*(1-tj_y)*(1-tj_z)--不知道你这里的tj_x,tj_y,tj_z是那里来的?
,单品返货率
from mx_yywd a,(
select 产品编码=isnull(a.cpbh,b.cpbh)
,名称=isnull(a.name,b.name)
,出货总数=isnull(a.出货总数,0)
,返货总数=isnull(b.返货总数,0)
,正价出货数=isnull(a.正价出货数,0)
,正价结算额=isnull(a.正价结算额,0)
,特价出货数=isnull(a.特价出货数,0)
,特价结算额=isnull(a.特价结算额,0)
,单品返货率=cast(cast(isnull(b.返货总数,0)*100.0
/(isnull(a.出货总数,0)-isnull(b.返货总数,0))
as decimal(10,2)) as varchar)+'%'
from(
select cpbh,name
,出货总数=sum(isum)
,正价出货数=sum(case tjflag when 0 then isum else 0 end)
,正价结算额=sum(case tjflag when 0 then isum*jg_zj else 0 end)
,特价出货数=sum(case tjflag when 1 then isum else 0 end)
,特价结算额=sum(case tjflag when 1 then isum*jg_zj else 0 end)
from chd_cpmx
group by cpbh,name
)a full join(
select cpbh,name,返货总数=sum(isum)
from fhd_cpmx
group by cpbh,name
)b on a.cpbh=b.cpbh and a.name=b.name
)b
大X,Y,Z是正价商品的扣点1,2,3,应当到(chd_yywd)这个表里找,每个店铺都不一样。
tj_x,tj_y,tj_z是特价商品的扣点1,2,3,(chd_cpmx)这个表里有。
应该在这个查询出加上
我试着修改了一下,可数据又是双倍的了。不知怎么修改了。declare @strdate varchar(12),
@EndDate varchar(12)
set @strdate='2004-04-30'
set @EndDate='2004-04-30'
select a.cpbh,a.name
,出货总数=sum(a.isum)
,正价出货数=sum(case a.tjflag when 0 then a.isum else 0 end)
,正价结算额=sum(case a.tjflag when 0 then a.isum*a.jg_zj*(1-b.X)*(1-b.Y)*(1-b.Z) else 0 end)
,特价出货数=sum(case a.tjflag when 1 then a.isum else 0 end)
,特价结算额=sum(case a.tjflag when 1 then a.isum*a.jg_zj*(1-a.tj_x)*(1-a.tj_x)*(1-a.tj_x) else 0 end)--
from chd_cpmx as a,chd_yywd as b
where (a.lrsj between @Strdate and @EndDate) and a.wdid=b.wdid
group by a.cpbh,a.name
@EndDate varchar(12)
select @strdate='2004-04-30'
,@EndDate='2004-04-30'select 产品编码=isnull(a.cpbh,b.cpbh)
,名称=isnull(a.name,b.name)
,出货总数=isnull(a.出货总数,0)
,返货总数=isnull(b.返货总数,0)
,正价出货数=isnull(a.正价出货数,0)
,正价结算额=isnull(a.正价结算额,0)
,特价出货数=isnull(a.特价出货数,0)
,特价结算额=isnull(a.特价结算额,0)
,合计单品结算额=isnull(a.正价结算额,0)+isnull(a.特价结算额,0)
,单品返货率=cast(cast(isnull(b.返货总数,0)*100.0
/(isnull(a.出货总数,0)-isnull(b.返货总数,0))
as decimal(10,2)) as varchar)+'%'
from(
select a.cpbh,a.name
,出货总数=sum(a.isum)
,正价出货数=sum(case a.tjflag when 0 then a.isum else 0 end)
,正价结算额=sum(case a.tjflag when 0 then a.isum*a.jg_zj else 0 end
*(1-b.X)*(1-b.Y)*(1-b.Z) )
,特价出货数=sum(case a.tjflag when 1 then a.isum else 0 end)
,特价结算额=sum(case a.tjflag when 1 then a.isum*a.jg_zj else 0 end
*(1-c.tj_x)*(1-c.tj_y)*(1-c.tj_z))
from chd_cpmx a
left join chd_yywd b on a.tjflag=0 and a.wdid=b.wdid
left join mx_yywd c on a.tjflag=1 and a.wdid=c.wdid
where a.lrsj between @Strdate and @EndDate
group by a.cpbh,a.name
)a full join(
select cpbh,name,返货总数=sum(isum)
from fhd_cpmx
where a.lrsj between @Strdate and @EndDate
group by cpbh,name
)b on a.cpbh=b.cpbh and a.name=b.name
left join 和 full join 有什么区别帮助里没找到(苯,初学)。
还有其它的用法吗?详细讲讲。谢谢
declare @a table(ida int)
insert @a select 1
union all select 2declare @b table(idb int)
insert @b select 2
union all select 3--内连接
select * from @a a join @b b on a.ida=b.idb/*--测试结果
只返回两个表中ida与idb相同的记录
ida idb
----------- -----------
2 2(所影响的行数为 1 行)
--*/
--左连接
select * from @a a left join @b b on a.ida=b.idb/*--测试结果
返回@a(左边表)所有的记录,及@b的idb与@a的ida对应的记录
没有对应的就用null表示ida idb
----------- -----------
1 NULL
2 2(所影响的行数为 2 行)
--*/
--右连接
select * from @a a right join @b b on a.ida=b.idb/*--测试结果
返回@b(右边表)所有的记录,及@b的idb与@a的ida对应的记录
没有对应的就用null表示ida idb
----------- -----------
2 2
NULL 3(所影响的行数为 2 行)
--*/
--全连接
select * from @a a full join @b b on a.ida=b.idb/*--测试结果
返回@a与@b的所有记录,没有对应的用NULL表示ida idb
----------- -----------
2 2
NULL 3
1 NULL(所影响的行数为 3 行)
--*/
产品编码,名称,
sum(出货总数) as出货总数,
sum(返货总数) as 返货总数,
sum(正价出货数) as 正价出货数,
sum(正价结算额) as 正价结算额,
sum(特价出货数) as 特价出货数,
sum(特价结算额) as 特价结算额,
sum(合计单品结算额) as 合计单品结算额,
sum(返货总数)/(sum(出货总数)-sum(返货总数)) as 单品返货率
from (
select
b.cpbh as 产品编码,
b.name as 名称,
b.isum as 出货总数,
0 as 返货总数,
case when tjflag=0 then b.isum else 0 end as 正价出货数,
case when tjflag=0 then b.isum*b.jg_z*(1-a.x)*(1-a.y)*(1-a.z) else 0 end as 正价结算额,
case when tjflag<>0 then b.isum else 0 end as 特价出货数,
case when tjflag<>0 then b.isum*b.jg_z*(1-b.tj_x)*(1-b.tj_y)*(1-b.tj_z) else 0 end as 特价结算额,
case when tjflag=0 then b.isum*b.jg_z*(1-a.x)*(1-a.y)*(1-a.z) else b.isum*b.jg_z*(1-b.tj_x)*(1-b.tj_y)*(1-b.tj_z) end as 合计单品结算额
from mx_yywd a,chd_cpmx b
where a.wdid=b.wdid
union all
select
b.cpbh as 产品编码,
b.name as 名称,
0 as 出货总数,
b.isum as 返货总数,
0 as 正价出货数,
0 as 正价结算额,
0 as 特价出货数,
0 as 特价结算额,
0 as 合计单品结算额
from mx_yywd a,fhd_cpmx b
where a.wdid=b.wdid
) as x
group by 产品编码,名称