货品信息表T_Goods
采购货品入库表T_Import
采购货品退货表T_Outport
三个表都有GoodsCode货品编码字段
其中货品信息表T_Goods还有GoodsName货品名称,GoodsUnit货品规格,UnitS单位字段如何获取
我想获取每种货品编码的以下资料货品编码|货品名称|货品规格|货品单位|采购数量|采购金额|退货数量|退货金额|合计数量|合计金额
采购货品入库表T_Import
采购货品退货表T_Outport
三个表都有GoodsCode货品编码字段
其中货品信息表T_Goods还有GoodsName货品名称,GoodsUnit货品规格,UnitS单位字段如何获取
我想获取每种货品编码的以下资料货品编码|货品名称|货品规格|货品单位|采购数量|采购金额|退货数量|退货金额|合计数量|合计金额
采购货品入库表T_Import
采购货品退货表T_Outport
三个表的结构给出来啊 我怎么知道有什么字段啊?
create table T_Goods(
id int IDENTITY(1,1) primary key, --编号
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsType varchar(50), --货品类别 /code
GoodsUnit varchar(50), --货品规格
BigUnits varchar(10), --大单位
SmallUnits varchar(10), --小单位
BigPprice float, --大单位批发价
SmallPprice float, --小单位批发价
BigLprice float, --大单位零售价
SmallLprice float, --小单位零售价
zengsanbi int, --整散比
DepotUp float, --库存上限
DepotDown float, --库存下限
memo varchar(200)) --备注
--创建采购货品入库表
create table T_Import(
id int IDENTITY(1,1) primary key, --编号
ImportID varchar(50), --入库单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
InPrice float, --入库价
Outdate varchar(200), --过期时间
Product varchar(200), --供应商
OnBillCode varchar(50), --对应的单号
memo varchar(200), --备注
jingbanren varchar(50), --经办人
zhizuoren varchar(50), --制作人
Addtime datetime) --入库时间--创建采购货品退货表
create table T_Outport(
id int IDENTITY(1,1) primary key, --编号
OutportID VARCHAR(50), --退货单号
depotname varchar(50), --仓库名称
Barcode varchar(50), --条形码
GoodsCode varchar(50), --货品编码
GoodsName varchar(200), --货品名称
GoodsUnit varchar(50), --货品规格
units varchar(10), --单位
Number float, --数量
InPrice float, --入库价
Outdate varchar(200), --过期时间
Product varchar(200), --供应商
OnBillCode varchar(50), --对应的入库单号
memo varchar(200), --备注
jingbanren varchar(50), --经办人
zhizuoren varchar(50), --制作人
Addtime smalldatetime) --退货时间
货品编码|货品名称|货品规格|大单位|小单位|采购数量|采购金额|退货数量|退货金额|合计数量|合计金额是统计每种货品编码的信息DengXingJie(杰西)
帮下手
from T_Goods as a, T_Import as b, T_Outport as c
where a.GoodsCode = b.GoodsCode and c.GoodsCode = b.GoodsCode
group by a.GoodsCode as 货品编码, a.GoodsName as 货品名称, GoodsUnit as 货品规格, BigUnits as 大单位, SmallUnits as 小单位
order by a.GoodsCode
你的运行有点问题
我修改了一下怎么只有一条数据的
select a.GoodsType as 货品类型,a.GoodsCode as 货品编码, a.GoodsName as 货品名称, a.GoodsUnit as 货品规格, a.BigUnits as 大单位, a.SmallUnits as 小单位, Sum(b.Number) as 采购数量, Sum(b.Number * b.InPrice) as 采购金额, Sum(c.Number) as 退货数量, Sum(c.Number * c.InPrice) as 退货金额, Sum(b.Number + c.Number) as 合计数量, Sum(b.Number * b.InPrice + c.Number * c.InPrice) as 合计金额
from T_Goods as a, T_Import as b, T_Outport as c
where a.GoodsCode = b.GoodsCode and c.GoodsCode = b.GoodsCode
group by a.GoodsType,a.GoodsCode , a.GoodsName, a.GoodsUnit, a.BigUnits, a.SmallUnits
order by a.GoodsType改成这样,但是运行就只有一条数据,为何
我里面的货品编码和货品类型有几个的
from T_Goods as a, T_Import as b, T_Outport as c
where a.GoodsCode = b.GoodsCode and c.GoodsCode = b.GoodsCode
group by a.GoodsType,a.GoodsCode , a.GoodsName, a.GoodsUnit, a.BigUnits, a.SmallUnits
order by a.GoodsTypeunionselect a.GoodsType as 货品类型,a.GoodsCode as 货品编码, a.GoodsName as 货品名称, a.GoodsUnit as 货品规格, a.BigUnits as 大单位, a.SmallUnits as 小单位, Sum(b.Number) as 采购数量, Sum(b.Number * b.InPrice) as 采购金额, 0 as 退货数量, 0 as 退货金额, Sum(b.Number) as 合计数量, Sum(b.Number * b.InPrice) as 合计金额
from T_Goods as a, T_Import as b
where a.GoodsCode = b.GoodsCode and a.GoodsCode Not in(select GoodsCode From T_Outport)
group by a.GoodsType,a.GoodsCode , a.GoodsName, a.GoodsUnit, a.BigUnits, a.SmallUnits
order by a.GoodsType
在关键字 'union' 附近有语法错误。不成!
from T_Goods as a
left join (select GoodsCode,sum(采购数量) as a1 ,sum(采购数量*采购单价) as a2 from T_Import groub by GoodsCode) as b
on (a.GoodsCode = b.GoodsCode)
left join (select GoodsCode,sum(退货数量) as a1 ,sum(退货数量*采购单价) as a2 from T_Outport groub by GoodsCode) as b
on (a.GoodsCode = b.GoodsCode)
用这一段代替 ()内容:
b.a1 - c.a1,b.a2-c.a2另外a.[汉字]中的汉字,请用实际的字段名代替
from
T_Goods m join
(select GoodsCode,a.number as number_a,a.qty as qty_a,isnull(b.number,0) as number_b,isnull(b.qty,0) as qty_b,a.number-isnull(b.number,0) as number_sum,a.qty-isnull(b.qty,0) as qty_sum
from
(select GoodsCode ,sum(number) as number,sum(inprice*number) as qty from T_Import GROUP BY GoodsCode ) a left join
(select GoodsCode ,sum(number) as number,sum(inprice*number) as qty from T_Outport GROUP BY GoodsCode ) b on a.GoodsCode=b.GoodsCode) n on m.GoodsCode=n.GoodsCode由於沒有數據所以也不好測試,你自己試試
這根據你的意思只對"货品编码"分組,不對供應商分組,也就是說不能供應商所提供的相同料號都會匯兌在一起
这个是我根据你昨天给我回答而改写的
select b.GoodsCode as 货品编码,b.GoodsName as 货品名称,b.GoodsUnit as 货品规格,b.number as 进货数量,b.qty as 进货金额,isnull(c.number,0) as 退货数量,isnull(c.qty,0) as 退货金额,isnull((isnull(b.number,0)-isnull(c.number,0)),0) as 合计数量,isnull((isnull(b.qty,0)-isnull(c.qty,0)),0) as 合计金额 from
(select GoodsCode,GoodsName,GoodsUnit,isnull(sum(number),0) as number,isnull(sum(inprice*number),0) as qty from T_Import GROUP BY GoodsCode,GoodsName,GoodsUnit) b left join
(select GoodsCode,GoodsName,GoodsUnit,isnull(sum(number),0) as number,isnull(sum(inprice*number),0) as qty from T_Outport GROUP BY GoodsCode,GoodsName,GoodsUnit) c on b.GoodsCode=c.GoodsCode left join
(select GoodsType,goodscode from T_GOODS where goodstype='01') as d on b.goodscode=d.goodscode
但是我想对货品类别进行统计,意思就是如何在上面的代码里添加一个条件为相同的货品类别来进行统计呢