gcbh----工程编号
kcid----库存ID
clsl----出库数量
ckdj----出库单价
gcbh kcid clsl ckdj
2004092711 543 18 10
2004092711 543 20 10
2004092711 580 88 .5
2004092711 381 6 7.647
2004092711 580 30 .5
表:tld
gcbh----工程编号
kcid----库存ID
clsl----退料数量
tljg----退料单价
gcbh kcid clsl tljg
2004092711 543 10 9
2004092711 580 50 .4
求一条SQL语句,要求列出此工程的最后工程用料结算汇总,即出库单减去退料单之后的汇总。
要求查询结果如下所示:
gcbh kcid clsl zj
2004092711 543 28 290
2004092711 580 60 35
2004092711 381 6 45.882
今天结帐,谢谢!
15 个解决方案
#1
select a.gcbh,a.kcid,clsl=sum(a.clsl)-sum(b.clsl),zj=sum(a.clsl)*a.ckdj-sum(b.clsl)*b.tljg
from ckd a join tld b on a.gcbh=b.gcbh
group by gcbh,kcid
from ckd a join tld b on a.gcbh=b.gcbh
group by gcbh,kcid
#2
select gcbh,kcid,sum(clsl) as clsl,sum(zj) as zj
from (
select gcbh,kcid,clsl,ckdj*clsl as zj from ckd
union all
select gcbh,kcid,-clsl,-tljg*clsl as zj from tld
) as t
错了不管!!
哈,哈哈,哈哈哈......
from (
select gcbh,kcid,clsl,ckdj*clsl as zj from ckd
union all
select gcbh,kcid,-clsl,-tljg*clsl as zj from tld
) as t
错了不管!!
哈,哈哈,哈哈哈......
#3
TO:zonelive(peter)
麻烦您测试一下好吗?好象查询结果不符合要求啊。谢谢!
麻烦您测试一下好吗?好象查询结果不符合要求啊。谢谢!
#4
SELECT gcbh, ckid, SUM(clsl) AS Expr1, SUM(clsl * ckdj) AS Expr2
FROM (SELECT gcbh, ckid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid AS ckid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, ckid
FROM (SELECT gcbh, ckid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid AS ckid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, ckid
#5
SELECT gcbh, kcid, SUM(clsl) AS Expr1, SUM(clsl * ckdj) AS Expr2
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid , (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, kcid
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid , (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, kcid
#6
Select gcbh,kcid,sum(clsl),sum(ckdj*clsl) as clsl from
(
Select gcbh, kcid,clsl,ckdj from ckd
union all
Select gcbh,kcid,-clsl ,-tljg from tld
)k group by gcbh,kcid
#7
select gcbh,kcid,clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
#8
--测试
--测试数据
create table ckd(gcbh varchar(10),kcid int,clsl int,ckdj decimal(10,3))
insert ckd select '2004092711',543,18,10
union all select '2004092711',543,20,10
union all select '2004092711',580,88,.5
union all select '2004092711',381,6 ,7.647
union all select '2004092711',580,30,.5
create table tld(gcbh varchar(10),kcid int,clsl int,tljg decimal(10,3))
insert tld select '2004092711',543,10,9
union all select '2004092711',580,50,.4
go
--查询
select gcbh,kcid,clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
go
--删除测试
drop table ckd,tld
/*--测试结果
gcbh kcid clsl zj
---------- ----------- ----------- ------------
2004092711 381 6 45.882
2004092711 543 28 290.000
2004092711 580 68 39.000
(所影响的行数为 3 行)
--*/
--测试数据
create table ckd(gcbh varchar(10),kcid int,clsl int,ckdj decimal(10,3))
insert ckd select '2004092711',543,18,10
union all select '2004092711',543,20,10
union all select '2004092711',580,88,.5
union all select '2004092711',381,6 ,7.647
union all select '2004092711',580,30,.5
create table tld(gcbh varchar(10),kcid int,clsl int,tljg decimal(10,3))
insert tld select '2004092711',543,10,9
union all select '2004092711',580,50,.4
go
--查询
select gcbh,kcid,clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
go
--删除测试
drop table ckd,tld
/*--测试结果
gcbh kcid clsl zj
---------- ----------- ----------- ------------
2004092711 381 6 45.882
2004092711 543 28 290.000
2004092711 580 68 39.000
(所影响的行数为 3 行)
--*/
#9
1.楼主自己的那个结果有错误
2.关联的时候应该用union all,否则两个表有重复的记录的时候,计算会出错
3.也可以用 left join 来解决:
select gcbh=isnull(a.gcbh,b.gcbh)
,kcid=isnull(a.kcid,b.kcid)
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,kcid
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
2.关联的时候应该用union all,否则两个表有重复的记录的时候,计算会出错
3.也可以用 left join 来解决:
select gcbh=isnull(a.gcbh,b.gcbh)
,kcid=isnull(a.kcid,b.kcid)
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,kcid
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
#10
呵呵,谢谢大家,基本上解决了!小弟现在的表ckd中还有gcmc(工程名称)和clmc(材料名称)两个字段,可是表tld中却没有,我想把这两个字段在查询的时候也列出来怎么写啊?再次麻烦大家了,谢谢!!!
gcbh gcmc kcid clmc clsl zj
---------- -------- ----- ------ ------ --------
2004092711 测试工程 381 材料A 6 45.882
2004092711 测试工程 543 材料B 28 290.000
2004092711 测试工程 580 材料C 68 39.000
gcbh gcmc kcid clmc clsl zj
---------- -------- ----- ------ ------ --------
2004092711 测试工程 381 材料A 6 45.882
2004092711 测试工程 543 材料B 28 290.000
2004092711 测试工程 580 材料C 68 39.000
#11
--方法1.
select gcbh,gcmc=max(gcmc),kcid,clmc=max(clmc),clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,gcmc,kcid,clmc,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,'',kcid,'',-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
select gcbh,gcmc=max(gcmc),kcid,clmc=max(clmc),clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,gcmc,kcid,clmc,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,'',kcid,'',-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
#12
Select ckd.gcbh,ckd.gcmc,ckd.kcid,ckd.clmc,aaa.clsl,aaa.zj from ckd
left join
(
Select gcbh,kcid,sum(clsl)as clsl,sum(ckdj*clsl) as zj from
(
Select gcbh, kcid,clsl,ckdj from ckd
union all
Select gcbh,kcid,-clsl ,-tljg from tld
)k group by gcbh,kcid
)aaa
on ckd.gcbh=aaa.gcbh and ckd.kcid=aaa.kcid
#13
--方法2.
select gcbh=isnull(a.gcbh,b.gcbh),a.gcmc
,kcid=isnull(a.kcid,b.kcid),a.clmc
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,gcmc,kcid,clmc,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,gcmc,kcid,clmc
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
select gcbh=isnull(a.gcbh,b.gcbh),a.gcmc
,kcid=isnull(a.kcid,b.kcid),a.clmc
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,gcmc,kcid,clmc,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,gcmc,kcid,clmc
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
#14
好了,搞定了,谢谢大家及时帮助!好象分少了点, 不好意思啊! ^_^
#15
SELECT ckd.gcmc, ckd.clmc, aa.gcbh, aa.kcid, SUM(aa.clsl) AS Expr1,
SUM(aa.clsl * aa.ckdj) AS Expr2
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld) aa INNER JOIN
ckd ON aa.gcbh = ckd.gcbh
GROUP BY ckd.gcmc, ckd.clmc, aa.gcbh, aa.kcid
SUM(aa.clsl * aa.ckdj) AS Expr2
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld) aa INNER JOIN
ckd ON aa.gcbh = ckd.gcbh
GROUP BY ckd.gcmc, ckd.clmc, aa.gcbh, aa.kcid
#1
select a.gcbh,a.kcid,clsl=sum(a.clsl)-sum(b.clsl),zj=sum(a.clsl)*a.ckdj-sum(b.clsl)*b.tljg
from ckd a join tld b on a.gcbh=b.gcbh
group by gcbh,kcid
from ckd a join tld b on a.gcbh=b.gcbh
group by gcbh,kcid
#2
select gcbh,kcid,sum(clsl) as clsl,sum(zj) as zj
from (
select gcbh,kcid,clsl,ckdj*clsl as zj from ckd
union all
select gcbh,kcid,-clsl,-tljg*clsl as zj from tld
) as t
错了不管!!
哈,哈哈,哈哈哈......
from (
select gcbh,kcid,clsl,ckdj*clsl as zj from ckd
union all
select gcbh,kcid,-clsl,-tljg*clsl as zj from tld
) as t
错了不管!!
哈,哈哈,哈哈哈......
#3
TO:zonelive(peter)
麻烦您测试一下好吗?好象查询结果不符合要求啊。谢谢!
麻烦您测试一下好吗?好象查询结果不符合要求啊。谢谢!
#4
SELECT gcbh, ckid, SUM(clsl) AS Expr1, SUM(clsl * ckdj) AS Expr2
FROM (SELECT gcbh, ckid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid AS ckid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, ckid
FROM (SELECT gcbh, ckid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid AS ckid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, ckid
#5
SELECT gcbh, kcid, SUM(clsl) AS Expr1, SUM(clsl * ckdj) AS Expr2
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid , (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, kcid
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid , (0 - clsl) AS clsl, tljg AS ckdj
FROM tld)
GROUP BY gcbh, kcid
#6
Select gcbh,kcid,sum(clsl),sum(ckdj*clsl) as clsl from
(
Select gcbh, kcid,clsl,ckdj from ckd
union all
Select gcbh,kcid,-clsl ,-tljg from tld
)k group by gcbh,kcid
#7
select gcbh,kcid,clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
#8
--测试
--测试数据
create table ckd(gcbh varchar(10),kcid int,clsl int,ckdj decimal(10,3))
insert ckd select '2004092711',543,18,10
union all select '2004092711',543,20,10
union all select '2004092711',580,88,.5
union all select '2004092711',381,6 ,7.647
union all select '2004092711',580,30,.5
create table tld(gcbh varchar(10),kcid int,clsl int,tljg decimal(10,3))
insert tld select '2004092711',543,10,9
union all select '2004092711',580,50,.4
go
--查询
select gcbh,kcid,clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
go
--删除测试
drop table ckd,tld
/*--测试结果
gcbh kcid clsl zj
---------- ----------- ----------- ------------
2004092711 381 6 45.882
2004092711 543 28 290.000
2004092711 580 68 39.000
(所影响的行数为 3 行)
--*/
--测试数据
create table ckd(gcbh varchar(10),kcid int,clsl int,ckdj decimal(10,3))
insert ckd select '2004092711',543,18,10
union all select '2004092711',543,20,10
union all select '2004092711',580,88,.5
union all select '2004092711',381,6 ,7.647
union all select '2004092711',580,30,.5
create table tld(gcbh varchar(10),kcid int,clsl int,tljg decimal(10,3))
insert tld select '2004092711',543,10,9
union all select '2004092711',580,50,.4
go
--查询
select gcbh,kcid,clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,kcid,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,kcid,-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
go
--删除测试
drop table ckd,tld
/*--测试结果
gcbh kcid clsl zj
---------- ----------- ----------- ------------
2004092711 381 6 45.882
2004092711 543 28 290.000
2004092711 580 68 39.000
(所影响的行数为 3 行)
--*/
#9
1.楼主自己的那个结果有错误
2.关联的时候应该用union all,否则两个表有重复的记录的时候,计算会出错
3.也可以用 left join 来解决:
select gcbh=isnull(a.gcbh,b.gcbh)
,kcid=isnull(a.kcid,b.kcid)
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,kcid
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
2.关联的时候应该用union all,否则两个表有重复的记录的时候,计算会出错
3.也可以用 left join 来解决:
select gcbh=isnull(a.gcbh,b.gcbh)
,kcid=isnull(a.kcid,b.kcid)
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,kcid
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
#10
呵呵,谢谢大家,基本上解决了!小弟现在的表ckd中还有gcmc(工程名称)和clmc(材料名称)两个字段,可是表tld中却没有,我想把这两个字段在查询的时候也列出来怎么写啊?再次麻烦大家了,谢谢!!!
gcbh gcmc kcid clmc clsl zj
---------- -------- ----- ------ ------ --------
2004092711 测试工程 381 材料A 6 45.882
2004092711 测试工程 543 材料B 28 290.000
2004092711 测试工程 580 材料C 68 39.000
gcbh gcmc kcid clmc clsl zj
---------- -------- ----- ------ ------ --------
2004092711 测试工程 381 材料A 6 45.882
2004092711 测试工程 543 材料B 28 290.000
2004092711 测试工程 580 材料C 68 39.000
#11
--方法1.
select gcbh,gcmc=max(gcmc),kcid,clmc=max(clmc),clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,gcmc,kcid,clmc,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,'',kcid,'',-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
select gcbh,gcmc=max(gcmc),kcid,clmc=max(clmc),clsl=sum(clsl),zj=sum(zj)
from(
select gcbh,gcmc,kcid,clmc,clsl,zj=clsl*ckdj from ckd
union all
select gcbh,'',kcid,'',-clsl,-clsl*tljg from tld
)a group by gcbh,kcid
#12
Select ckd.gcbh,ckd.gcmc,ckd.kcid,ckd.clmc,aaa.clsl,aaa.zj from ckd
left join
(
Select gcbh,kcid,sum(clsl)as clsl,sum(ckdj*clsl) as zj from
(
Select gcbh, kcid,clsl,ckdj from ckd
union all
Select gcbh,kcid,-clsl ,-tljg from tld
)k group by gcbh,kcid
)aaa
on ckd.gcbh=aaa.gcbh and ckd.kcid=aaa.kcid
#13
--方法2.
select gcbh=isnull(a.gcbh,b.gcbh),a.gcmc
,kcid=isnull(a.kcid,b.kcid),a.clmc
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,gcmc,kcid,clmc,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,gcmc,kcid,clmc
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
select gcbh=isnull(a.gcbh,b.gcbh),a.gcmc
,kcid=isnull(a.kcid,b.kcid),a.clmc
,clsl=isnull(a.clsl,0)-isnull(b.clsl,0)
,zj=isnull(a.zj,0)-isnull(b.zj,0)
from(
select gcbh,gcmc,kcid,clmc,clsl=sum(clsl),zj=sum(clsl*ckdj)
from ckd group by gcbh,gcmc,kcid,clmc
)a full join(
select gcbh,kcid,clsl=sum(clsl),zj=sum(clsl*tljg)
from tld group by gcbh,kcid
)b on a.gcbh=b.gcbh and a.kcid=b.kcid
#14
好了,搞定了,谢谢大家及时帮助!好象分少了点, 不好意思啊! ^_^
#15
SELECT ckd.gcmc, ckd.clmc, aa.gcbh, aa.kcid, SUM(aa.clsl) AS Expr1,
SUM(aa.clsl * aa.ckdj) AS Expr2
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld) aa INNER JOIN
ckd ON aa.gcbh = ckd.gcbh
GROUP BY ckd.gcmc, ckd.clmc, aa.gcbh, aa.kcid
SUM(aa.clsl * aa.ckdj) AS Expr2
FROM (SELECT gcbh, kcid, clsl, ckdj
FROM ckd
UNION
SELECT gcbh, kcid, (0 - clsl) AS clsl, tljg AS ckdj
FROM tld) aa INNER JOIN
ckd ON aa.gcbh = ckd.gcbh
GROUP BY ckd.gcmc, ckd.clmc, aa.gcbh, aa.kcid