DROP TABLE bdcubasdoc,bdcumandoc,icgeneralh,icgeneralb,bdinvcl
create table bdcubasdoc(bd_cubasdocpk_cubasdoc varchar(10),bd_cubasdoccustname varchar(10)) /*客商档案:客商档案主键/客商名称 */
insert into bdcubasdoc values('a1','广州好地')
insert into bdcubasdoc values('a2','广州力百')
insert into bdcubasdoc values('a3','浙江那爱司')
create table bdcumandoc(bd_cumandocpk_cubasdoc varchar(10),bd_cumandocpk_cumandoc varchar(10))/*客商管理档案:客商档案主键/客商管理档案主键*/
insert into bdcumandoc values('a1','1')
insert into bdcumandoc values('a2','2')
insert into bdcumandoc values('a3','3')create table icgeneralh(ic_general_hcgeneralhid int ,ic_general_hcproviderid varchar(3),ic_general_hccustomerid varchar(3),ic_general_hcdispatcherid varchar(5))
/* 出入库单表头:出入库单表头id/供应商ID/客户ID/收发类型id*/
insert into icgeneralh values(101,'2','','s')
insert into icgeneralh values(102,'2','','s')
insert into icgeneralh values(103,'','3','s')
insert into icgeneralh values(104,'','3','s')
insert into icgeneralh values(105,'3','','s')
insert into icgeneralh values(106,'','1','s')create table icgeneralb(ic_general_bcgeneralhid int ,ic_general_bnoutnum int,ic_general_bninnum int,ic_general_bcinvbasid varchar(20))
/*出入库单表体:出入库单表头id/实出数量/实入数量/存货基本id */
insert into icgeneralb values(101,5,'','K1')
insert into icgeneralb values(102,'',2,'K2')
insert into icgeneralb values(103,4, '','K1')
insert into icgeneralb values(104,'',3,'K1')
insert into icgeneralb values(105,'',8,'K3')
insert into icgeneralb values(106,3,'','K3')
create table bdinvcl(bd_invbasdocpk_invbasdoc varchar(20),bd_invbasdocinvname varchar(20))
/*存货管理档案:存货档案主键/存货名称*/
insert into bdinvcl values('k1','AES')
insert into bdinvcl values('K2','包装桶')
insert into bdinvcl values('K3','AE03')goselect bd_cubasdoccustname as g01,bd_invbasdocinvname as g02 ,sum(isnull(a.ic_general_bnoutnum,0) - isnull(a.ic_general_bninnum,0)) as g03
from bdcubasdoc join bdcumandoc on bd_cubasdocpk_cubasdoc=bd_cumandocpk_cubasdoc
join
(select ic_general_hcproviderid,ic_general_bnoutnum,ic_general_bninnum,ic_general_bcinvbasid
from icgeneralh inner join icgeneralb
on ic_general_hcgeneralhid=ic_general_bcgeneralhid
where ic_general_hccustomerid=' '
union all
select ic_general_hccustomerid,ic_general_bnoutnum,ic_general_bninnum ,ic_general_bcinvbasid
from icgeneralh inner join icgeneralb on ic_general_hcgeneralhid=ic_general_bcgeneralhid
where ic_general_hcproviderid=' ') a
on bd_cumandocpk_cumandoc=a.ic_general_hcproviderid left outer join bdinvcl on bd_invbasdocpk_invbasdoc=a.ic_general_bcinvbasid
group by bd_cubasdoccustname,bd_invbasdocinvname服务器: 消息 213,级别 16,状态 5,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。(所影响的行数为 5 行)
create table bdcubasdoc(bd_cubasdocpk_cubasdoc varchar(10),bd_cubasdoccustname varchar(10)) /*客商档案:客商档案主键/客商名称 */
insert into bdcubasdoc values('a1','广州好地')
insert into bdcubasdoc values('a2','广州力百')
insert into bdcubasdoc values('a3','浙江那爱司')
create table bdcumandoc(bd_cumandocpk_cubasdoc varchar(10),bd_cumandocpk_cumandoc varchar(10))/*客商管理档案:客商档案主键/客商管理档案主键*/
insert into bdcumandoc values('a1','1')
insert into bdcumandoc values('a2','2')
insert into bdcumandoc values('a3','3')create table icgeneralh(ic_general_hcgeneralhid int ,ic_general_hcproviderid varchar(3),ic_general_hccustomerid varchar(3),ic_general_hcdispatcherid varchar(5))
/* 出入库单表头:出入库单表头id/供应商ID/客户ID/收发类型id*/
insert into icgeneralh values(101,'2','','s')
insert into icgeneralh values(102,'2','','s')
insert into icgeneralh values(103,'','3','s')
insert into icgeneralh values(104,'','3','s')
insert into icgeneralh values(105,'3','','s')
insert into icgeneralh values(106,'','1','s')create table icgeneralb(ic_general_bcgeneralhid int ,ic_general_bnoutnum int,ic_general_bninnum int,ic_general_bcinvbasid varchar(20))
/*出入库单表体:出入库单表头id/实出数量/实入数量/存货基本id */
insert into icgeneralb values(101,5,'','K1')
insert into icgeneralb values(102,'',2,'K2')
insert into icgeneralb values(103,4, '','K1')
insert into icgeneralb values(104,'',3,'K1')
insert into icgeneralb values(105,'',8,'K3')
insert into icgeneralb values(106,3,'','K3')
create table bdinvcl(bd_invbasdocpk_invbasdoc varchar(20),bd_invbasdocinvname varchar(20))
/*存货管理档案:存货档案主键/存货名称*/
insert into bdinvcl values('k1','AES')
insert into bdinvcl values('K2','包装桶')
insert into bdinvcl values('K3','AE03')goselect bd_cubasdoccustname as g01,bd_invbasdocinvname as g02 ,sum(isnull(a.ic_general_bnoutnum,0) - isnull(a.ic_general_bninnum,0)) as g03
from bdcubasdoc join bdcumandoc on bd_cubasdocpk_cubasdoc=bd_cumandocpk_cubasdoc
join
(select ic_general_hcproviderid,ic_general_bnoutnum,ic_general_bninnum,ic_general_bcinvbasid
from icgeneralh inner join icgeneralb
on ic_general_hcgeneralhid=ic_general_bcgeneralhid
where ic_general_hccustomerid=' '
union all
select ic_general_hccustomerid,ic_general_bnoutnum,ic_general_bninnum ,ic_general_bcinvbasid
from icgeneralh inner join icgeneralb on ic_general_hcgeneralhid=ic_general_bcgeneralhid
where ic_general_hcproviderid=' ') a
on bd_cumandocpk_cumandoc=a.ic_general_hcproviderid left outer join bdinvcl on bd_invbasdocpk_invbasdoc=a.ic_general_bcinvbasid
group by bd_cubasdoccustname,bd_invbasdocinvname服务器: 消息 213,级别 16,状态 5,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213,级别 16,状态 1,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。(所影响的行数为 5 行)
create table bdcubasdoc(bd_cubasdocpk_cubasdoc varchar(10),bd_cubasdoccustname varchar(10)) /*客商档案:客商档案主键/客商名称 */
insert into bdcubasdoc values('a1','广州好地')
insert into bdcubasdoc values('a2','广州力百')
insert into bdcubasdoc values('a3','浙江那爱司')
create table bdcumandoc(bd_cumandocpk_cubasdoc varchar(10),bd_cumandocpk_cumandoc varchar(10))/*客商管理档案:客商档案主键/客商管理档案主键*/
insert into bdcumandoc values('a1','1')
insert into bdcumandoc values('a2','2')
insert into bdcumandoc values('a3','3') create table icgeneralh(ic_general_hcgeneralhid int ,ic_general_hcproviderid varchar(3),ic_general_hccustomerid varchar(3),ic_general_hcdispatcherid varchar(5))
/* 出入库单表头:出入库单表头id/供应商ID/客户ID/收发类型id*/
insert into icgeneralh values(101,'2','','s')
insert into icgeneralh values(102,'2','','s')
insert into icgeneralh values(103,'','3','s')
insert into icgeneralh values(104,'','3','s')
insert into icgeneralh values(105,'3','','s')
insert into icgeneralh values(106,'','1','s') create table icgeneralb(ic_general_bcgeneralhid int ,ic_general_bnoutnum int,ic_general_bninnum int,ic_general_bcinvbasid varchar(20))
/*出入库单表体:出入库单表头id/实出数量/实入数量/存货基本id */
insert into icgeneralb values(101,5,'','K1')
insert into icgeneralb values(102,'',2,'K2')
insert into icgeneralb values(103,4, '','K1')
insert into icgeneralb values(104,'',3,'K1')
insert into icgeneralb values(105,'',8,'K3')
insert into icgeneralb values(106,3,'','K3')
create table bdinvcl(bd_invbasdocpk_invbasdoc varchar(20),bd_invbasdocinvname varchar(20))
/*存货管理档案:存货档案主键/存货名称*/
insert into bdinvcl values('k1','AES')
insert into bdinvcl values('K2','包装桶')
insert into bdinvcl values('K3','AE03')
go select bd_cubasdoccustname as g01,bd_invbasdocinvname as g02 ,sum(isnull(a.ic_general_bnoutnum,0) - isnull(a.ic_general_bninnum,0)) as g03
from bdcubasdoc join bdcumandoc on bd_cubasdocpk_cubasdoc=bd_cumandocpk_cubasdoc
join
(select ic_general_hcproviderid,ic_general_bnoutnum,ic_general_bninnum,ic_general_bcinvbasid
from icgeneralh inner join icgeneralb
on ic_general_hcgeneralhid=ic_general_bcgeneralhid
where ic_general_hccustomerid=' '
union all
select ic_general_hccustomerid,ic_general_bnoutnum,ic_general_bninnum ,ic_general_bcinvbasid
from icgeneralh inner join icgeneralb on ic_general_hcgeneralhid=ic_general_bcgeneralhid
where ic_general_hcproviderid=' ') a
on bd_cumandocpk_cumandoc=a.ic_general_hcproviderid left outer join bdinvcl on bd_invbasdocpk_invbasdoc=a.ic_general_bcinvbasid
group by bd_cubasdoccustname,bd_invbasdocinvname
/*
g01 g02 g03
---------- -------------------- -----------
广州好地 AE03 3
浙江那爱司 AE03 -8
广州力百 AES 5
浙江那爱司 AES 1
广州力百 包装桶 -2(所影响的行数为 5 行)
*/DROP TABLE bdcubasdoc,bdcumandoc,icgeneralh,icgeneralb,bdinvcl
先运行一下: DROP TABLE bdcubasdoc,bdcumandoc,icgeneralh,icgeneralb,bdinvcl
广州好地 AE03 3
浙江那爱司 AE03 -8
广州力百 AES 5
浙江那爱司 AES 1
广州力百 包装桶 -2没错