SELECT NULLIF(A。sparecode ,B。sparecode ) sparecode ,累计入库, 累计出库
FROM (select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) as '累计出库' from checkout inner join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode)A FULL JOIN (select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) as '累计入库' from planin inner join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) B ON A。sparecode =B。sparecode
FROM (select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) as '累计出库' from checkout inner join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode)A FULL JOIN (select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) as '累计入库' from planin inner join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) B ON A。sparecode =B。sparecode
select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode) a left join (
select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) b
on a.sparecode=b.sparecode
FROM (select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) as '累计出库' from checkout inner join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode)A FULL JOIN (select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) as '累计入库' from planin inner join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) B ON A。sparecode =B。sparecode
left join 好象有点问题
如果有累计入库 ,无累计出库 呢
select b.sparecode,b.累计入库,a.累计出库 from (
select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode) a right join (
select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) b
on a.sparecode=b.sparecode
RIGHT join 好象有点问题
如果本区间无累计入库 ,有累计出库 呢
非常感谢!
问题暂时是解决了,可是我也不太确信计算的是否都准。由于我查的是某一时间段的累计,所以有不入库就出库的现象。:)
SELECT COALESCE(A.sparecode ,B.sparecode ) sparecode ,ISNULL(累计入库,0) 累计入库, ISNULL(累计出库,0) 累计出库
FROM (select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) as '累计出库' from checkout inner join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode)A FULL JOIN (select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) as '累计入库' from planin inner join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) B ON A.sparecode =B.sparecode
不行啊,你的执行报错呀. 假设我有一万个产品,那么是不是应该返回一万条呢?
FROM (select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) as '累计出库' from checkout inner join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode)A FULL JOIN (select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) as '累计入库' from planin inner join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode) B ON A.sparecode =B.sparecode
只出现本区间 有入库或有出库的记录
我的输入法有问题,所有符号是全角,不能转换
在试试
left join
(
select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode
) b
on a.sparecode=b.sparecode
left join
(
select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode
) c
on a.sparecode=c.sparecode
left join
(
select checkoutlist.sparecode,sum(isnull(checkoutlist.amount,0)) as 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode
) a
on a.sparecode=stored.sparecode
left join
(
select planinlist.sparecode,sum(isnull(planinlist.factamount,0)) as 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode
) b
on a.sparecode=b.sparecode
我执行了.是返回stored 的所有条数了,但是为什么数据都是空的呀!
SqlDumpExceptionHandler: 进程 51 发生了严重的异常 c0000005 EXCEPTION_ACCESS_VIOLATION。SQL Server 将终止该进程。连接中断
left join
(
select checkoutlist.sparecode,sum(checkoutlist.amount) as 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkoutlist.sparecode ='026100024W'and checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode
) a
on a.sparecode=stored.sparecode
left join
(
select planinlist.sparecode,sum(planinlist.factamount) as 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planinlist.sparecode='026100024W' and planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode
) b
on a.sparecode=b.sparecode
-----------------------------
我执行了.是返回stored 的所有条数了,但是为什么数据都是空的呀!
因为你在这个时间段没有这些产品
left join
(
select checkoutlist.sparecode,sum(checkoutlist.amount) as 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode
) a
on a.sparecode=stored.sparecode
left join
(
select planinlist.sparecode,sum(planinlist.factamount) as 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode
) b
on a.sparecode=b.sparecode会没数据?
left join
(
select checkoutlist.sparecode,sum(checkoutlist.amount) as 累计出库 from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkout.createdate between '2003-01-01'and '2003-05-31'
group by checkoutlist.sparecode
) b
on a.sparecode=b.sparecode
left join
(
select planinlist.sparecode,sum(planinlist.factamount) as 累计入库 from planin join planinlist on planin.objid=planinlist.bindparent
where planin.finishdate between '2003-01-01'and '2003-05-31'
group by planinlist.sparecode
) c
on a.sparecode=c.sparecode你不要乱改,才发现被你改动了~~
a left join b
a left join c
这样的结构
left join
(
select checkoutlist.sparecode,sum(checkoutlist.amount) [累计出库] from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkout.createdate between cast('2003-01-01' as datetime) and cast('2003-05-31' as datetime)
group by checkoutlist.sparecode
) b
on a.sparecode=b.sparecode
left join
(
select planinlist.sparecode,sum(planinlist.factamount) [累计入库] from planin join planinlist on planin.objid=planinlist.bindparent
where planin.finishdate between cast('2003-01-01' as datetime) and cast('2003-05-31' as datetime)
group by planinlist.sparecode
) c
on a.sparecode=c.sparecode
left join
(
select checkoutlist.sparecode,sum(checkoutlist.amount) [累计出库] from checkout join checkoutlist on checkout.objid=checkoutlist.bindparent
where checkout.createdate between cast('2003-01-01' as datetime) and cast('2003-05-31' as datetime)
group by checkoutlist.sparecode
) b
on a.sparecode=b.sparecode
left join
(
select planinlist.sparecode,sum(planinlist.factamount) [累计入库] from planin join planinlist on planin.objid=planinlist.bindparent
where planin.finishdate between cast('2003-01-01' as datetime) and cast('2003-05-31' as datetime)
group by planinlist.sparecode
) c
on a.sparecode=c.sparecode