有一客户表--user, 字段是客户号,客户名字和客户买股票开户的分店
user_id, user_name, user_store股票表--acc,字段是 客户号, 帐号, 股票的币种(人民币或美元), 账户的余额
user_id(PK), acc_number, acc_type, acc_balance汇率表--rat, 字段是 币种, 人民币的汇率, 美元的汇率
acc_type, rat_rmb, rat_usd现求一SQL,是想要在107分店开户的所有客户的客户号,每个客户所拥有的账号数(几个帐号),和余额(余额折算成人民币)
谢谢!
user_id, user_name, user_store股票表--acc,字段是 客户号, 帐号, 股票的币种(人民币或美元), 账户的余额
user_id(PK), acc_number, acc_type, acc_balance汇率表--rat, 字段是 币种, 人民币的汇率, 美元的汇率
acc_type, rat_rmb, rat_usd现求一SQL,是想要在107分店开户的所有客户的客户号,每个客户所拥有的账号数(几个帐号),和余额(余额折算成人民币)
谢谢!
and a.user_store in (select u.user_store from user where uuser_store = '107')
union all
select 0 accnum,a.acc_balance*r.rat_usd bal from acc a,rat r where a.acc_type = r.acctype
不知道你的具体算法,也没有数据参考,也不知道对不对,就当个参考吧
select u.user_id uid,count(a.*) as numCount,
balance=case when r.acc_type='RMB' then
sum(r.acc_balance)*(select rat.acc_type from rat where acc_type='RMB')
else
sum(r.acc_balance)*(select rat.acc_type from rat where acc_type='USA')
end
from user u inner join acc a on u.user_id=a.user_id
innre join rat r on a.acc_type=r.acc_type
select u.user_id uid,count(a.*) as numCount,
balance=case when a.acc_type='RMB' then
sum(r.acc_balance)*(select rat.acc_type from rat where acc_type='RMB')
else
sum(r.acc_balance)*(select rat.acc_type from rat where acc_type='USA')
end
from user u inner join acc a on u.user_id=a.user_id
innre join rat r on a.acc_type=r.acc_type
第二行 [balance=case when r.acc_type='RMB' then] -> [balance=case when a.acc_type='RMB' then]没测试
case acct_type
when 'RMB' then acc_balance
when 'USA' then acc_balance*rat_usd
from……
left join
(
select b.acc_number,count(b.acc_type) as cn,
sum(case when b.rat_rmb='RMB' then b.acc_balance
when b.rat_rmb='MY' then b.acc_balance*8
else 0 end) as sm from acc b group by b.acc_number
) d on a.acc_number=d.acc_number
where a.user_store='107'
select u.user_id,count(a.acc_number),
case a.acc_type
when 'RMB' then
a.acc_balance
when 'USD' then
a.acc_balance*r.rat_rmb
end as balance
from user u,acc a,rat r
where u.user_id = a.user_id
and a.acc_type = r.acc_type
and u.user_store = '107'
group by u.user_id