select sum(shuliang) as
数量,huoName from ruku group by huoName
select sum(shuliang) as 数量,name from haocai group by nameruku是入库表,haocai是出库表
这是执行出来的结果。我想得到各个配件剩余的数量是多少以各个配件的名称进行分组。
数量,huoName from ruku group by huoName
select sum(shuliang) as 数量,name from haocai group by nameruku是入库表,haocai是出库表
这是执行出来的结果。我想得到各个配件剩余的数量是多少以各个配件的名称进行分组。
select name,sum(isnull(rnum,0)) rnum,sum(isnull(cnum,0)) cnum,
sum(isnull(rnum,0)) - sum(isnull(cnum,0)) as sumnum
from(
select name,sum(shuliang) as rnum,0 as cnum from ruku group by huoName
union all
select name,0 as rnum,sum(shuliang) as cnum from haocai group by name
)t
group by name
;with m1 as(
select sum(shuliang) as 数量,huoName from ruku group by huoName
),m2 as(
select sum(shuliang) as 数量,name from haocai group by name)select a.huoName,a.数量-b.数量 from m1 a left join m2 b on a.huoName=b.name
select name,sum(isnull(rnum,0)) rnum,sum(isnull(cnum,0)) cnum,
sum(isnull(rnum,0)) - sum(isnull(cnum,0)) as sumnum
from(
select huoName as name,sum(shuliang) as rnum,0 as cnum from ruku group by huoName
union all
select name,0 as rnum,sum(shuliang) as cnum from haocai group by name
)t
group by name
这个,5楼的有问题!
(select sum(shuliang) as
数量,huoName from ruku group by huoName )a left join
(select sum(shuliang) as 数量,name from haocai group by name)b
on a.huoname=b.huoname
不知道行不行
(select sum(shuliang) as
数量,huoName from ruku group by huoName )a left join
(select sum(shuliang) as 数量,name from haocai group by name)b
on a.huoname=b.huoname
更正一下
(SELECT sum(shuliang) AS [数量],huoName from ruku group by huoName) a
LEFT JOIN
(select sum(shuliang) as [数量],name from haocai group by NAME) b
ON a.huoName=b.NAME
;with m1 as(
select sum(shuliang) as 数量,huoName from ruku group by huoName ),
m2 as( select sum(shuliang) as 数量,name from haocai group by name) select a.huoName,[剩余数量]=isnull(a.数量,0)-isnull(b.数量,0) from m1 a
left join m2 b on a.huoName=b.name用isnull函数处理一下