sql 中易犯错：用别名作为筛选条件

如 错误代码：

  select ii.id as 投资编号,
       ii.PROTOCOL_NO as 合同编号,
       'F' || CR_CUSTOMER_NUMBER || '-' || ii.LENDING_No as 出借编号,
       cu.cr_name as 客户姓名,ii.loan_return_date,
       (case ii.loan_return_date
         when '00120001' then
          '1日'
         else
          '16日'
       end) as loan_retunedate,
       (case ii.regular_type
         when '00130001' then
          trim('.' from to_char(ii.invest_amt, 'FM999,999,999.99'))
         else
          trim('.' from to_char(ii.Regular_Amt, 'FM999,999,999.99'))
       end) as amt,
       to_char(ii.agreed_delivery_date, 'yyyy-MM-dd'),
       td.data_name,
       wf.node_name,
       (case ii.regular_type
         when '00130001' then
          to_char(ii.invest_start_date, 'yyyy-MM-dd')
         else
          to_char(ii.regular_start_date, 'yyyy-MM-dd')
       end) as end_date,
       to_char(ii.create_date, 'yyyy-MM-dd')
  from ft_t_invest_info ii
  left join crmFT.crm_customer@To_Crm cu
    on ii.customer_id = cu.id
  left join sys_t_dictionary td
    on ii.product_type = td.data_code
  left join ft_t_flow_def wf
    on wf.node_code = ii.flow_id
 where cu.cr_customer_number not in (1, 2, 3, 4)
   and loan_return_date ='1日'

 

错误1：用别名作为筛选条件

错误2：‘1日’是结果集后显示，结果集不包含‘1日’，包含‘0012000’

 

 

正确一 sql:

select ii.id as 投资编号,
       ii.PROTOCOL_NO as 合同编号,
       'F' || CR_CUSTOMER_NUMBER || '-' || ii.LENDING_No as 出借编号,
       cu.cr_name as 客户姓名,ii.loan_return_date,
       (case ii.loan_return_date
         when '00120001' then
          '1日'
         else
          '16日'
       end) as loan_retunedate,
       (case ii.regular_type
         when '00130001' then
          trim('.' from to_char(ii.invest_amt, 'FM999,999,999.99'))
         else
          trim('.' from to_char(ii.Regular_Amt, 'FM999,999,999.99'))
       end) as amt,
       to_char(ii.agreed_delivery_date, 'yyyy-MM-dd'),
       td.data_name,
       wf.node_name,
       (case ii.regular_type
         when '00130001' then
          to_char(ii.invest_start_date, 'yyyy-MM-dd')
         else
          to_char(ii.regular_start_date, 'yyyy-MM-dd')
       end) as end_date,
       to_char(ii.create_date, 'yyyy-MM-dd')
  from ft_t_invest_info ii
  left join crmFT.crm_customer@To_Crm cu
    on ii.customer_id = cu.id
  left join sys_t_dictionary td
    on ii.product_type = td.data_code
  left join ft_t_flow_def wf
    on wf.node_code = ii.flow_id
 where cu.cr_customer_number not in (1, 2, 3, 4)
   and ii.loan_return_date ='00120001'

 

正确二 sql:

select * from (select ii.id as 投资编号,
       ii.PROTOCOL_NO as 合同编号,
       'F' || CR_CUSTOMER_NUMBER || '-' || ii.LENDING_No as 出借编号,
       cu.cr_name as 客户姓名,ii.loan_return_date,
       (case ii.loan_return_date
         when '00120001' then
          '1日'
         else
          '16日'
       end) as loan_retunedate,
       (case ii.regular_type
         when '00130001' then
          trim('.' from to_char(ii.invest_amt, 'FM999,999,999.99'))
         else
          trim('.' from to_char(ii.Regular_Amt, 'FM999,999,999.99'))
       end) as amt,
       to_char(ii.agreed_delivery_date, 'yyyy-MM-dd'),
       td.data_name,
       wf.node_name,
       (case ii.regular_type
         when '00130001' then
          to_char(ii.invest_start_date, 'yyyy-MM-dd')
         else
          to_char(ii.regular_start_date, 'yyyy-MM-dd')
       end) as end_date,
       to_char(ii.create_date, 'yyyy-MM-dd')
  from ft_t_invest_info ii
  left join crmFT.crm_customer@To_Crm cu
    on ii.customer_id = cu.id
  left join sys_t_dictionary td
    on ii.product_type = td.data_code
  left join ft_t_flow_def wf
    on wf.node_code = ii.flow_id
  where loan_retunedate='1日'