这个语句怎么写？（在线等！）

eason2000 2004-09-22 09:09:33
select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli into #A from pf_ckmx group by ddwid


select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli into #b from pf_thmx group by ddwid


我要用一个语句查询上面两个表中的数据，但是两个临时表中的关联字段DDWID的值不相同。也就是在#A中有的DDWID，在#B中不一定有。
...全文
122 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
----相加
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.maoli,0) as maoli

from (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid


ddwid jiashhj chengbje maoli
----------- ------- --------- -----
DDW00000001 5432.40 6355.9080 .00
DDW00000003 2735.32 462509.5644 335531.64
DDW00000004 10.00 2307.9888 1677.47
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
create table pf_ckmx (ddwid varchar (11),jiashhj decimal (12,2),chengbje decimal (12,2),maoli decimal (12,2))
create table pf_thmx (ddwid varchar (11),jiashhj decimal (12,2),chengbje decimal (12,2),maoli decimal (12,2))

insert into pf_ckmx
select 'DDW00000001',3768.20,3768.20,0.00 union
select 'DDW00000003',1367.66,197653.66,167765.82


insert into pf_thmx
select 'DDW00000001',1664.20,1664.20,.00 union
select 'DDW00000003',1367.66,197653.66,167765.82 union
select 'DDW00000004',10.00,1972.6434,1677.47

----相减
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)-isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)-isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)-isnull (b.maoli,0) as maoli

from (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid

ddwid jiashhj chengbje maoli
----------- ------- --------- -----
DDW00000001 2104.00 2461.6800 .00
DDW00000003 .00 .0000 .00
DDW00000004 -10.00 -2307.9888 -1677.47

----相加
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.maoli,0) as maoli

from (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid


ddwid jiashhj chengbje maoli
----------- ------- --------- -----
DDW00000001 5432.40 6355.9080 .00
DDW00000003 .00 .0000 .00
DDW00000004 10.00 2307.9888 1677.47
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)-isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)-isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)-isnull (b.maoli,0) as maoli

from (select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_ckmx group by ddwid) a full join
(select ddwid,sum(jiashhj) jiashhj,sum(chengbje*1.17) chengbje,sum(maoli) maoli from pf_thmx group by ddwid) b
on a.ddwid=b.ddwid
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
我想不是相加吧
一个是批发出库明细pf_ckmx
一个是批发退货明细pf_thmx
我想应该是相减吧
如果相加了
单位金额就不对了吧！！！
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
上面写错了

---

SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (b.jiashhj,0) as jiashhj,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.maoli,0) as maoli

from #a full join
#b
on a.ddwid=b.ddwid
eason2000 2004-09-22
  • 打赏
  • 举报
 回复
字段是一样的。但是数据不一致。

----------------- #a数据
DDW00000001 3768.20 3768.2190 .00
DDW00000003 3431.45 2941.2396 418.98

--------------#b 数据

DDW00000001 1664.20 1664.2080 .00
DDW00000003 1367.66 197653.6692 167765.82
DDW00000004 10.00 1972.6434 1677.47


我查询出来的数据要求DDW00000001，DDW00000003的数据进行汇总，同时也要把DDW00000004的数据
也查询出来。



谢谢了
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0)+isnull (a.chengbje,0) as chengbje,
isnull (a.maoli,0)+isnull (b.jiashhj,0) as maoli,
isnull (a.chengbje,0)+isnull (b.chengbje,0) as chengbje
from #a full join
#b
on a.ddwid=b.ddwid
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
可是你插入临时表的数据都是一样的！！
eason2000 2004-09-22
  • 打赏
  • 举报
 回复
如果两个表中的DDWID相等，那么汇总JIASHHJ，CHENGBJE，MAOLI。不相等，提取原来的数据
yesterday2000 2004-09-22
  • 打赏
  • 举报
 回复
具体不知道你想达到什么目的

SELECT
ISNULL (A.DDWID,b.ddwid ) as ddwid ,
isnull (a.jiashhj,0) as jiashhj,
isnull (a.chengbje,0) as chengbje,
isnull (a.maoli,0) as maoli,
isnull (b.jiashhj,0) as bjiashhj,
isnull (b.chengbje,0) as bchengbje,
isnull (b.maoli,0) as bmaoli
from #a full join
#b
on a.ddwid=b.ddwid
chinaandys 2004-09-22
  • 打赏
  • 举报
 回复
select a.* from #A a left join #B b
on a.DDWID=b.DDWID
chinaandys 2004-09-22
  • 打赏
  • 举报
 回复
select a.* from #A aleft join #B b
on a.DDWID=b.DDWID
lxysjl 2004-09-22
  • 打赏
  • 举报
 回复
能说出一些数据出来吗.把想要的结果说出来
eason2000 2004-09-22
  • 打赏
  • 举报
 回复
问题解决。谢谢各位
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
用while语句实现九九乘法表
如代码所示 #include <stdio.h> int main() { int i,j; i=1; while(i<=9) { j=1; while(j<=i) { printf("%d*%d=%d",j,i,i*j); printf(" "); j++; } printf("\n"); i++; } return 0...
疑惑：如何用hql语句查询前10条数据？？求解！！！

如果不用Criteria，而用Spring的this.getHibernateTemplate()怎么获取前10条数据？
hql语句又该如何写？
下面请看具体的问题，先贴图：
 
 

 
我想根据clicks查询出Top 10的记录，怎么实现啊？
网上查了很多资料，几乎查不到我想要的。。。。
等待高人解决~~~
mysql更新的语句怎么写_mysql更新语句怎么写？
MySQL更新语句也就是MySQL中的update语句，当我们需要更新或者修改表中的数据时，就会使用这个update语句，下面我们就来看一下mysql更新语句的具体写法。MySQL中update语句用于更新表中的现有数据。亦可用UPDATE语句来更改表中单个行，一组行或所有行的列值。MySQL中UPDATE语句的语法：单表UPDATE [LOW_PRIORITY] [IGNORE] table_r...
mysql更新数据的语句怎么写_mysql更新数据库语句怎么写?mysql更新字段语句
大家对于mysql都不陌生吧，那么mysql更新数据库语句应该怎样写呢?下面要和大家介绍的就是这方面的内容，一起来了解一下吧。MySQL当中update语句用来更新表中的现有数据。可以用UPDATE语句来更改表中单个行，一组行或者是所有行的列值。MySQL中UPDATE语句的语法：单表多表用update语句修改表的时候：1、limit子句代表限定修改数据的行数2、单表修改指的就是修改指定单个表当中...
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章