buguge - Keep it simple,stupid

知识就是力量,但更重要的,是运用知识的能力why buguge?

导航

系统运营支持-如何快速协助处理excel里的数据?来吧,教你一招

今天,一个销售MM发来一个excel文件,里面是客户的结算数据。需要我们技术协助,为excel里每一行的个人银行卡号,匹配出来所对应的银行机构编码和银行名称。

下方是excel文件里其中一部分数据的截图。

预期结果是:

 

 

这要依托我们系统里的卡bin数据来完成。查询卡bin的sql是:select * from card_bin where card_bin=left('[银行卡号]', card_bin_len) and card_len=length('[银行卡号]')

 

 

我发给小组里的一个小伙来处理。

小伙是怎么做的?

小伙见一共有60多条,考虑到编写批处理sql比较耗费时间,就逐条卡号进行复制,然后贴到sql语句的where条件里,在我们的sql平台查询后,将结果再粘贴到对应的单元格里。如此repeat。

 

 

虽然数据量不大,但也会耗费十来分钟的时间,再者,需要全神贯注,以免粘贴错行。

 

 

对于不喜欢“如此repeat”的同学,针对这个小需求,我来介绍一个简单的方法。

我的方法,当然不是把excel导入到数据库里。

我的方法,也不是把excel导入到一个有权限的数据库,然后再把生产库的卡bin数据同步到这个数据库,然后使用join联表查询。

我的方法,是基于excel强大的公式和mysql强大的sql来搞。

首先,利用excel的公式。在空白列,使用公式“="union all select '"&D5&"' as cardNo"”。

然后,复制整列,我们得到一个mysql的临时表:

select '6223200335******' as cardNo
union all select '6227002169966******' as cardNo
union all select '6231190153303******' as cardNo
union all...

接下来,就是写最终的sql了:

select distinct b.cardNo, a.union_pay_account, a.iss_ins_nm,a.card_bin from card_bin a join(
select '6223200335******' as cardNo
union all select '6227002169966******' as cardNo
union all select '6231190153303******' as cardNo
union all...
 )b on card_bin=left(cardNo, card_bin_len) and card_len=length(cardNo)

执行sql,得到结果。

接下来,把结果粘贴到excel里。搞定!

 

 

 

posted on 2024-07-19 09:00  buguge  阅读(90)  评论(0编辑  收藏  举报