我有这么一天SQL语句如下:
select distinct khmc,View_0355.scjhdh,View_0355.scgxkh,View_0355.cpgg,View_0355.schxh,View_0355.schx,
View_0355.psh,View_0355.mscl,View_0355.aa,
View_0355.xm,View_0372.ms,View_0372.cd,View_0372.lb,
View_0372.jz,View_0372.ygm from View_0355,View_0372 where View_0355.GXFL='成检' and View_0355.scjhdh='6c030'
and View_0355.scgxkh='00061833'
and View_0372.jhdh='6c030' and View_0372.lckh='00061833'
group by khmc,View_0355.scjhdh,View_0355.scgxkh,View_0355.cpgg,View_0355.schxh,View_0355.schx,
View_0355.psh,View_0355.mscl,View_0355.aa,View_0355.xm,View_0372.ms,View_0372.cd,View_0372.lb,
View_0372.jz,View_0372.ygm
scjhdh cpgg schxh schx psh mscl aa xm ms cd
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 20.00 发粘
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 84.00 白条
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 20.00 发粘
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 84.00 白条 我现在想要合计(mscl),及在上面结果上增加一列合计,理想现实下面的结果:scjhdh cpgg schxh schx psh mscl aa xm ms cd heji(A1+A2) bili(A2/heji)
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 20.00 发粘 3002.00 5.06%
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 84.00 白条 3002.00 5.06%
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 20.00 发粘 3002.00 5.06%
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 84.00 白条 3002.00 5.06%这下面是合计的SQL:select sum(mscl) as hj from View_0355 where View_0355.GXFL='成检' and View_0355.scjhdh='6c030'
and View_0355.scgxkh='00061833' 其实条件是一样,必须是和上面条件一样才能合计请问一下达到上面的结果,SQL该怎么写!
请各位一定要帮忙啊!
select distinct khmc,View_0355.scjhdh,View_0355.scgxkh,View_0355.cpgg,View_0355.schxh,View_0355.schx,
View_0355.psh,View_0355.mscl,View_0355.aa,
View_0355.xm,View_0372.ms,View_0372.cd,View_0372.lb,
View_0372.jz,View_0372.ygm from View_0355,View_0372 where View_0355.GXFL='成检' and View_0355.scjhdh='6c030'
and View_0355.scgxkh='00061833'
and View_0372.jhdh='6c030' and View_0372.lckh='00061833'
group by khmc,View_0355.scjhdh,View_0355.scgxkh,View_0355.cpgg,View_0355.schxh,View_0355.schx,
View_0355.psh,View_0355.mscl,View_0355.aa,View_0355.xm,View_0372.ms,View_0372.cd,View_0372.lb,
View_0372.jz,View_0372.ygm
scjhdh cpgg schxh schx psh mscl aa xm ms cd
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 20.00 发粘
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 84.00 白条
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 20.00 发粘
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 84.00 白条 我现在想要合计(mscl),及在上面结果上增加一列合计,理想现实下面的结果:scjhdh cpgg schxh schx psh mscl aa xm ms cd heji(A1+A2) bili(A2/heji)
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 20.00 发粘 3002.00 5.06%
00061833 色丁花 6D1769 紫 S 152.0000000 A2 xxxx 84.00 白条 3002.00 5.06%
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 20.00 发粘 3002.00 5.06%
00061833 色丁花 6D1769 紫 S 2850.0000000 A1 xxxx 84.00 白条 3002.00 5.06%这下面是合计的SQL:select sum(mscl) as hj from View_0355 where View_0355.GXFL='成检' and View_0355.scjhdh='6c030'
and View_0355.scgxkh='00061833' 其实条件是一样,必须是和上面条件一样才能合计请问一下达到上面的结果,SQL该怎么写!
请各位一定要帮忙啊!
select distinct khmc,a.scjhdh,a.scgxkh,a.cpgg,a.schxh,a.schx,
a.psh,a.mscl,a.aa,
a.xm,b.ms,b.cd,b.lb,
b.jz,b.ygm,
(select sum(mscl) as hj
from View_0355
where GXFL=a.GXFL and scjhdh=a.scjhdh
and scgxkh=a.scgxkh) as heji,
ltrim(cast(a.mscl*100./(select sum(mscl) as hj
from View_0355
where GXFL=a.GXFL and scjhdh=a.scjhdh
and scgxkh=a.scgxkh) as decimal(10,2)))+'%' as bili
from View_0355 a,View_0372 b
where a.GXFL='成检' and a.scjhdh='6c030'
and a.scgxkh='00061833'
and b.jhdh='6c030' and View_0372.lckh='00061833'
group by khmc,a.scjhdh,a.scgxkh,a.cpgg,a.schxh,a.schx,
a.psh,a.mscl,a.aa,a.xm,b.ms,b.cd,b.lb,
b.jz,b.ygm
select distinct khmc,a.scjhdh,a.scgxkh,a.cpgg,a.schxh,a.schx,
a.psh,a.mscl,a.aa,
a.xm,b.ms,b.cd,b.lb,
b.jz,b.ygm,
(select sum(mscl) as hj
from View_0355
where GXFL=a.GXFL and scjhdh=a.scjhdh
and scgxkh=a.scgxkh) as heji,
ltrim(cast(a.mscl*100./(select sum(mscl) as hj
from View_0355
where GXFL=a.GXFL and scjhdh=a.scjhdh
and scgxkh=a.scgxkh) as decimal(10,2)))+'%' as bili
from View_0355 a,View_0372 b
where a.GXFL='成检' and a.scjhdh='6c030'
and a.scgxkh='00061833'
and b.jhdh='6c030' and b.lckh='00061833'
group by khmc,a.scjhdh,a.scgxkh,a.cpgg,a.schxh,a.schx,
a.psh,a.mscl,a.aa,a.xm,b.ms,b.cd,b.lb,
b.jz,b.ygm
;with ach as
(
select distinct khmc,a.scjhdh,a.scgxkh,a.cpgg,a.schxh,a.schx,
a.psh,a.mscl,a.aa,
a.xm,b.ms,b.cd,b.lb,
b.jz,b.ygm
from View_0355 a,View_0372 b
where a.GXFL='成检' and a.scjhdh='6c030'
and a.scgxkh='00061833'
and b.jhdh='6c030' and b.lckh='00061833'
group by khmc,a.scjhdh,a.scgxkh,a.cpgg,a.schxh,a.schx,
a.psh,a.mscl,a.aa,a.xm,b.ms,b.cd,b.lb,
b.jz,b.ygm
)select *,
(select sum(mscl) from ach) as heji,
ltrim(cast((select top 1 mscl from ach
where khmc = t.khmc and cpgg=t.cpgg and schxh=t.schxh and schx=t.schx and psh=t.psh and aa='A2')*100./
(select sum(mscl) from ach) as decimal(10,2)))+'%' as bili
from ach t