V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
KagamineLenKai2
V2EX  ›  MySQL

从开发那里要来了一段 SQL,表示看不懂……

  •  
  •   KagamineLenKai2 · 2016-08-24 13:39:48 +08:00 · 4788 次点击
    这是一个创建于 3012 天前的主题,其中的信息可能已经有所发展或是发生改变。
    SELECT CASE a.send_underlying
    WHEN '1' THEN '已放标'
    WHEN '2' THEN '未放标'
    ELSE ''
    END
    AS isSendUnderlying,
    '' isSettleReloan,
    CASE a.is_ex_apply
    WHEN '1' THEN '新增'
    WHEN '2' THEN '展期'
    ELSE ''
    END
    AS isExApply,
    ad.marketing_manager AS marketingManager,
    ad.sales_name AS salesName,
    a.org_city_name AS orgCityName,
    c.contract_number AS contractNumber,
    a.product_name AS productName,
    a.client_name AS clientName,
    a.id_number AS idNumber,
    c.loan_amount AS loanAmount,
    cp.surplus_principal AS surplusPrincipal,
    CONCAT(a.loan_term, '-', cp.period) AS loanTerm,
    a.service_rates AS serviceRates,
    cp.front_service_charge AS frontServiceCharge,
    a.product_rates AS productRates,
    cp.amortized_interest AS amortizedInterest,
    cp.period_money AS periodMoney,
    CASE
    WHEN a.is_ex_apply = 1 AND cp.period = 0 THEN c.actual_amount
    ELSE ''
    END
    AS actualAmount,
    c.actual_loan_time AS actualLoanTime,
    <!-- CASE
    WHEN a.is_ex_apply = 2 AND cp.period = 0 THEN ctemp.minTime
    ELSE ''
    END
    AS minTime, -->
    case
    when a.is_ex_apply=1 and cp.period>1 then DATE_SUB(cp.repay_date,INTERVAL 30 Day)
    when a.is_ex_apply=2 then DATE_SUB(cp.repay_date ,INTERVAL 30 Day) else '--'
    end as minTime,
    cp.repay_date AS repayDate,
    cp.amortized_principal AS amortizedPrincipal,
    '' AS sjhkTime,
    '' AS actualPaymentAmount,
    '' AS actualSettlementAmount,
    CASE WHEN a.zExpStandar = 1 THEN ctemp.minTime ELSE '' END
    AS hgZqContinueExTime,
    '' AS oweBalance,
    '' AS defaultTime,
    '' AS defaultDays
    FROM contract_plan cp
    INNER JOIN apply a ON a.apply_id = cp.apply_id
    INNER JOIN apply_detail ad ON ad.apply_id = cp.apply_id
    INNER JOIN contract c ON c.apply_id = cp.apply_id
    LEFT JOIN
    ( SELECT min(cpt.repay_date) AS minTime,
    max(cpt.repay_date) AS maxTime,
    cpt.apply_id
    FROM contract_plan cpt
    GROUP BY cpt.apply_id) ctemp
    ON ctemp.apply_id = cp.apply_id
    INNER JOIN node_record n
    ON a.apply_id = n.apply_id
    AND n.node_code = 'END'
    AND n.is_in_node <![CDATA[<>]]> 1

    INNER JOIN node_record nr
    ON a.apply_id = nr.apply_id
    AND nr.node_code = 'loan'
    AND nr.status_code <![CDATA[>=]]> 10011200
    AND nr.status_code <![CDATA[<=]]> 10011700
    WHERE 1 = 1
    <isNotEmpty prepend="and" property="orgAuth">
    a.org_id in ($orgAuth$)
    </isNotEmpty>
    <isNotEmpty prepend="and" property="orgid">
    a.org_id =#orgid#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="clientName">
    a.client_name =#clientName#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="isExApply">
    a.is_ex_apply = #isExApply#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="repayDateBegin">
    date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[>=]]> #repayDateBegin#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="repayDateEnd">
    date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[<=]]> #repayDateEnd#
    </isNotEmpty>
    ORDER BY a.client_name, c.contract_number, cp.repay_date desc
    </select>

    只会 SELECT 、 FROM 、 INNER INTO 、 LIMIT 的弱鸡表示跪了……
    15 条回复    2017-05-16 17:40:57 +08:00
    KagamineLenKai2
        1
    KagamineLenKai2  
    OP
       2016-08-24 13:43:38 +08:00
    前半部分还好……函数什么的查一查也就知道用法了,不过结尾的那一串
    <isNotEmpty prepend="and" property="orgAuth">
    a.org_id in ($orgAuth$)
    </isNotEmpty>
    <isNotEmpty prepend="and" property="orgid">
    a.org_id =#orgid#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="clientName">
    a.client_name =#clientName#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="isExApply">
    a.is_ex_apply = #isExApply#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="repayDateBegin">
    date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[>=]]> #repayDateBegin#
    </isNotEmpty>
    <isNotEmpty prepend="and" property="repayDateEnd">
    date_format(cp.repay_date,'%Y-%m-%d %h:%i:%s') <![CDATA[<=]]> #repayDateEnd#
    </isNotEmpty>
    ORDER BY a.client_name, c.contract_number, cp.repay_date desc
    </select>

    这是 SQL 吗?
    woshihuzios123
        2
    woshihuzios123  
       2016-08-24 13:53:40 +08:00
    @KagamineLenKai2 百度了一下发现是 ibatis
    yanyuan2046
        3
    yanyuan2046  
       2016-08-24 13:54:55 +08:00
    看着像 P2P
    KagamineLenKai2
        4
    KagamineLenKai2  
    OP
       2016-08-24 13:56:44 +08:00
    @woshihuzios123 表示…还是不懂…
    est
        5
    est  
       2016-08-24 14:00:56 +08:00
    还行。。。请搜索 纯 SQL 实现 MD5 算法。
    Durandal01
        6
    Durandal01  
       2016-08-24 14:13:16 +08:00
    @KagamineLenKai2 这是 mybatis 配置文件里的写法,后面那段是拼接 SQL 用的。

    <isNotEmpty>里包着的部分表示在某个参数不为空( property 所指的参数)的情况下,加上这一段 SQL ,连接字用 prepend 里写的那个。

    举例说就是:

    <isNotEmpty prepend="and" property="orgAuth">
    a.org_id in ($orgAuth$)
    </isNotEmpty>

    表示 orgAuth 不为空 的情况下, SQL 语句里拼上 and a.org_id in 'orgAuth 的值'
    woshihuzios123
        7
    woshihuzios123  
       2016-08-24 14:58:38 +08:00
    @KagamineLenKai2 其实我也不懂,我只会 lambda
    lianyue
        8
    lianyue  
       2016-08-24 15:20:16 +08:00
    这 是我见过最长的 sql 查询 语句了
    daweilv
        9
    daweilv  
       2016-08-24 15:25:11 +08:00
    弱弱的问一下,这样的统计效率比放在程序里算高吗?
    oscarzhao
        10
    oscarzhao  
       2016-08-24 15:31:38 +08:00
    explain 一下看看性能如何
    server
        11
    server  
       2016-08-24 15:32:13 +08:00   ❤️ 1
    为了实现而实现,我死之后那管他洪水滔天。
    subpo
        12
    subpo  
       2016-08-24 15:32:30 +08:00
    @daweilv 高太多了
    odirus
        13
    odirus  
       2016-08-24 15:33:14 +08:00
    @daweilv

    从单次查询来讲效率还可以,不过不利于后期优化,拆分成多个查询之后可以有针对性地进行缓存以及其他优化。
    8bit
        14
    8bit  
       2016-08-24 15:36:58 +08:00
    子查询比多次 Join 效率要高吧
    arist
        15
    arist  
       2017-05-16 17:40:57 +08:00
    以前做 BI 统计,dba 写单个统计点 SQL,每一项都是一页,大概有几百个统计项。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2669 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 05:14 · PVG 13:14 · LAX 21:14 · JFK 00:14
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.