数据库--sql语句项目实战记录

这是目前写过最繁琐的sql ！！！

业务要求提取以下数据：

1、
客户编号、客户国籍、客户风险等级、客户职业、客户在我司管理资产规模、客户持有产品数、客户首次认购我司产品时间、客户住所（具体到城市）、所属团队（财富直营团队）、所属理财师、客户性别、客户年龄、是否家族信托客户（家族信托设立规模）

select t.c_custno 客户编号,
       t.c_industry 客户职业,
       t.c_address 客户住所,
       (select c_caption
          from tdictionary t2
         where t2.l_keyno = '6036'
           and t2.c_keyvalue = t.c_nationality) 客户国籍,
       (select sum(ts.f_realshares)
          from tstaticshares ts
         where ts.c_custno = t.c_custno) 持有资产规模,
       (select sum(tt.f_signbalance)
          from ttrustbenefitcert tt
         where tt.c_custno = t.c_custno) 累计资产规模,
       (select count(count(1))
          from tstaticshares ts
         where ts.c_custno = t.c_custno
           and ts.f_realshares > 0
         group by ts.c_fundcode) 持有产品数,
       (select count(count(1))
          from ttrustbenefitcert ts
         where ts.c_custno = t.c_custno
         group by ts.c_fundcode) 产品总数,
       (select min(tt.d_effectdate)
          from ttrustbenefitcert tt
         where tt.c_custno = t.c_custno) 最早认购时间,
       (select tb.c_nodevalue
          from tbrokertree tb
         where substr(f_getbrokerdept(f_getfundbrokerrelation(t.c_custno)),
                      1,
                      12) = tb.c_nodecode) 所属团队,
       f_getfundbrokerrelation(t.c_custno, '2', null, 0, 0) 所属理财师,
       decode(t.c_sex, '0', '女', '1', '男') 客户性别,
       (2020 - to_number(substr(nvl(t.c_birthday, 2020), 1, 4))) 客户年龄,
       decode((select decode(count(1), 0, '0', '1')
                from tbeneficiaryinfo_req t2
               where t2.c_virtualagencycode = 'j'
                 and t.c_custno = t2.c_custno
                 and t2.c_status not in ('4')),
              '0',
              '无',
              '1',
              '有') 是否持有家族信托产品
  from tcustomerinfo t
 where t.c_custtype = '1';

执行结果如下图：

2、分产品类别
将以上查询结果创建一张表：

create table wl_20200721 as
select t.c_custno 客户编号,
       t.c_industry 客户职业,
       t.c_address 客户住所,
       (select c_caption
          from tdictionary t2
         where t2.l_keyno = '6036'
           and t2.c_keyvalue = t.c_nationality) 客户国籍,
       (select sum(ts.f_realshares)
          from tstaticshares ts
         where ts.c_custno = t.c_custno) 持有资产规模,
       (select sum(tt.f_signbalance)
          from ttrustbenefitcert tt
         where tt.c_custno = t.c_custno) 累计资产规模,
       (select count(count(1))
          from tstaticshares ts
         where ts.c_custno = t.c_custno
           and ts.f_realshares > 0
         group by ts.c_fundcode) 持有产品数,
       (select count(count(1))
          from ttrustbenefitcert ts
         where ts.c_custno = t.c_custno
         group by ts.c_fundcode) 产品总数,
       (select min(tt.d_effectdate)
          from ttrustbenefitcert tt
         where tt.c_custno = t.c_custno) 最早认购时间,
       (select tb.c_nodevalue
          from tbrokertree tb
         where substr(f_getbrokerdept(f_getfundbrokerrelation(t.c_custno)),
                      1,
                      12) = tb.c_nodecode) 所属团队,
       f_getfundbrokerrelation(t.c_custno, '2', null, 0, 0) 所属理财师,
       decode(t.c_sex, '0', '女', '1', '男') 客户性别,
       (2020 - to_number(substr(nvl(t.c_birthday, 2020), 1, 4))) 客户年龄,
       decode((select decode(count(1), 0, '0', '1')
                from tbeneficiaryinfo_req t2
               where t2.c_virtualagencycode = 'j'
                 and t.c_custno = t2.c_custno
                 and t2.c_status not in ('4')),
              '0',
              '无',
              '1',
              '有') 是否持有家族信托产品
  from tcustomerinfo t
 where t.c_custtype = '1';

从表中分类别后查询：

select *
  from wl_20200721 t
 where exists
 (select 1
          from ttrustbenefitcert t4, tfundinfo t5
         where t4.c_custno = t.客户编号
           and t4.c_fundcode = t5.c_fundcode
           and (t5.c_fundname like '%祈年养%' or t5.c_fundname like '%春华2号%' or
               t5.c_fundname like '%钱江6号集%' or
               t5.c_fundname like '%启航第19号应%' or
               t5.c_fundname like '%江尊享3号%'));