sql 语句请教

[zestsnake]

balance 主数据表


balance_id int identity 10
balance_account nvarchar 50
balance_name nvarchar 50
balance_jjbh nvarchar 50
balance_jjxh nvarchar 50
balance_jfjs numeric 18
balance_balance_jf numeric 18
balance_dfjs numeric 18
balance_balance_df numeric 18
balance_subaccount nvarchar 10
balance_branch nvarchar 10
balance_cur nvarchar 10
balance_date date 10
balance_ManagerNum nvarchar 15
balance_branchSys nvarchar 10
=======================================================
balancetmp 临时表
balancetmp_account nvarchar 100
balancetmp_name nvarchar 100
balancetmp_jjbh nvarchar 100
balancetmp_jjxh nvarchar 100
balancetmp_jfjs numeric 20
balancetmp_balance_jf numeric 20
balancetmp_dfjs numeric 20
balancetmp_balance_df numeric 20
balancetmp_cur nvarchar 20
balancetmp_subaccount nvarchar 20
balancetmp_date datetime 16
balancetmp_branch nvarchar 20
======================================================

ManagerAccount 经理账号表
ManagerAcc_id int identity 10
ManagerAcc_account nvarchar 50
ManagerAcc_accName nvarchar 50
ManagerAcc_mangerNum nvarchar 15
ManagerAcc_mangerName nvarchar 15
ManagerAcc_branch nvarchar 10
ManagerAcc_cur nvarchar 5
ManagerAcc_subAcc nvarchar 10
ManagerAcc_Accbranch nvarchar 10


原先是对 balancetmp 中 balancetmp_account,balancetmp_name,balancetmp_subaccount 记录进行合并后插入 balance表中

insert into balance  (balance_account, balance_name,  balance_jjbh,balance_jjxh,balance_jfjs,  balance_balance_jf, 
        balance_dfjs, 
        balance_balance_df, 
        balance_subaccount, 
        balance_branch, 
        balance_cur, 
        balance_date) 
   select balancetmp_account as  balancetmp_account  , 
          balancetmp_name as balancetmp_name, 
          MAX(balancetmp_jjbh) as balancetmp_jjbh , 
          MAX(balancetmp_jjxh) as balancetmp_jjxh , 
          SUM(balancetmp_jfjs) as balancetmp_jfjs , 
          SUM(balancetmp_balance_jf) as balancetmp_balance_jf , 
          SUM(balancetmp_dfjs) as balancetmp_dfjs , 
          SUM(balancetmp_balance_df) as balancetmp_balance_df, 
          max(balancetmp_subaccount) as balancetmp_subaccount, 
          MAX(balancetmp_branch) as balancetmp_branch, 
          MAX(balancetmp_cur) as balancetmp_cur, 
          MAX(balancetmp_date) as balancetm_date 
          from balancetmp group by balancetmp_account,balancetmp_name,balancetmp_subaccount 

是这样的SQL 语句

现在还要和ManagerAccount链接 balancetmp 去join ManagerAccount；
其他都和原来差不多，只是要处理下 balance.balance_branchSys 字段
如果balancetmp_account.balancetmp_account 在ManagerAccount.ManagerAcc_account有，
那么balance.balance_branchSys=ManagerAccount.ManagerAcc_branch
否则balance.balance_branchSys=balancetmp.balancetmp_branch

[听雨停了]

最近怎么这么多重复的帖子

• 打赏
• 举报

回复 赞

[zestsnake]

浪费了150分

• 打赏
• 举报

回复 赞

[听雨停了]

引用 2 楼 zestsnake 的回复:

浪费了150分

结帖给分会返还50%给你

• 打赏
• 举报

回复 赞

[吉普赛的歌]

楼主再描述清楚一点吧， 希望得到什么样的结果？自己认为困难的地方在哪里？

• 打赏
• 举报

回复 赞

[zestsnake]

不好意思 ，重新描述下 balance 主数据表 balance_id int identity 10 balance_account nvarchar 50 balance_name nvarchar 50 balance_balance_jf numeric 18 balance_branch nvarchar 10 balance_ManagerNum nvarchar 15 balance_branchSys nvarchar 10 ==================================================== balancetmp 临时表 balancetmp_account nvarchar 100 balancetmp_name nvarchar 100 balancetmp_balance_jf numeric 20 balancetmp_branch nvarchar 20 测试数据 balancetmp_account balancetmp_name balancetmp_balance_jf balancetmp_branch 11020001 A公司 15.00 M01 11020002 B公司 12.00 M02 11020003 C公司 10.00 X03 11020003 C公司 12.00 X03 11020002 B公司 17.00 M02 11020004 D公司 13.00 M04 11020005 E公司 20.00 M05 11020005 E公司 21.00 M05 11020006 F公司 99.00 M01 11020007 G公司 199.00 M02 ========================================================= ManagerAccount 经理账号表 ManagerAcc_account nvarchar 50 ManagerAcc_mangerNum nvarchar 15 ManagerAcc_branch nvarchar 10 ManagerAcc_Accbranch nvarchar 10 测试数据 ManagerAcc_account ManagerAcc_mangerNum ManagerAcc_branch ManagerAcc_Accbranch 11020001 8801 M001 M001 11020002 8802 M002 M002 11020004 8804 M004 M004 11020005 8805 M005 M005 原先是对 balancetmp 中 balancetmp_account,balancetmp_name 记录进行合并后插入 balance表中 insert into balance (balance_account, balance_name, balance_jfjs, balance_balance_jf, balance_branch ) select balancetmp_account as balancetmp_account , balancetmp_name as balancetmp_name, SUM(balancetmp_jfjs) as balancetmp_jfjs , SUM(balancetmp_balance_jf) as balancetmp_balance_jf , MAX(balancetmp_branch) as balancetmp_branch, from balancetmp group by balancetmp_account,balancetmp_name 是这样的SQL 语句 现在还要和ManagerAccount链接 balancetmp 去join ManagerAccount； 其他都和原来差不多，只是要处理下 balance.balance_branchSys 字段 如果balancetmp.balancetmp_account 在ManagerAccount.ManagerAcc_account有， 那么balance.balance_branchSys=ManagerAccount.ManagerAcc_branch 否则balance.balance_branchSys=balancetmp.balancetmp_branch 生成的数据应该是这样 account name je manaernum branch 1102001 A公司 15.00 8801 M01 这个account在ManagerAccount中，所以branch就是ManagerAcc_branch =M001 1102002 B公司 29.00 (汇总的17+12） 8802 M02 这个account在ManagerAccount中，所以branch就是ManagerAcc_branch =M001 1102003 C公司 22.00 (汇总的10+12） --- X03 这个account不在ManagerAccount中，所以branch就是balancetmp_branch =x003 对应的ManagerAcc_mangerNum 也没，所以就是"---" 然后把这些数据插入balanc主数据表中 balance_account =>account balance_name => name balance_balance_jf =>je balance_branch => branch balance_ManagerNum => manaernum balance_branchSys => branch

• 打赏
• 举报

回复 赞

[zestsnake]

顶一下顶一下

• 打赏
• 举报

回复 赞

[zestsnake]

各路大神来帮个忙啊

• 打赏
• 举报

回复 赞