sql 语句请教

zestsnake zestsnake 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
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
...全文
评论 2
收藏 已收藏
分享
返回顶部
zestsnake
MS-SQL Server 34,877 成员 254,635 内容