首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
V2EX  ›  问与答

新接手项目都是这种 SQL , 各位大佬怎么看 。。。

  •  
  •   ghbaqi · 40 天前 · 3828 次点击
    这是一个创建于 40 天前的主题,其中的信息可能已经有所发展或是发生改变。
    <select id="findFundBgInfo" resultmap="fundBgInfo"> <![CDATA[ SELECT b.secu_id AS F_SECU_ID, b.fund_code AS TRD_CODE, b.fundfullname AS CHI_NAME, b.fundname AS SECU_SHT, b.companyid AS FMC_COM_ID, b.companyname AS FMC_COM_NAME, b.F_INFO_CUSTODIANBANK AS CUST_COM_NAME, IF (f_Pchredm_Pchstartdate IS NULL OR f_Pchredm_Pchstartdate>DATE_FORMAT(NOW(),'%Y%m%d') OR LOCATE('633001001',PUR_ST_temp)>0,'暂停申购', IF(LOCATE('633001002',PUR_ST_temp)>0,'暂停大额申购', IF(LOCATE('633001003',PUR_ST_temp)>0,'暂停定期定额申购', IF(LOCATE('633001004',PUR_ST_temp)>0,'暂停大额定期定额申购','开放申购'))) ) AS PUR_ST, IF (f_Info_Redmstartdate IS NULL OR f_Info_Redmstartdate>DATE_FORMAT(NOW(),'%Y%m%d') OR LOCATE('633001005',REDEM_ST_temp)>0,'暂停赎回', IF(LOCATE('633001006',PUR_ST_temp)>0,'暂停实时赎回','开放赎回') ) AS REDEM_ST, b.isQDII AS IS_QDII, b.fundtype AS INV_TYP_COM, (CASE b.fundtype WHEN '10100' THEN '股票型' WHEN '10200' THEN '债券型' WHEN '10300' THEN '货币型' WHEN '10400' THEN '混合型' WHEN '10600' THEN 'FOF' WHEN '10700' THEN '保本型' WHEN '10800' THEN '理财型' WHEN '10901' THEN 'QDII' WHEN '10905' THEN '指数型' ELSE '专户' END) AS INV_TYP_COM_DESC, DATE_FORMAT(c.TRADE_DT,'%Y-%m-%d') AS TRD_DT, nav.F_NAV_UNIT AS UNIT_NAV, c.F_AVGRETURN_DAY AS CHG_RAT_1D, c.F_AVGRETURN_WEEK AS CHG_RAT_1W, c.F_AVGRETURN_MONTH AS CHG_RAT_1M, c.F_AVGRETURN_QUARTER AS CHG_RAT_3M, c.F_AVGRETURN_HALFYEAR AS CHG_RAT_6M, c.F_AVGRETURN_YEAR AS CHG_RAT_1Y, c.F_AVGRETURN_TWOYEA AS CHG_RAT_2Y, c.F_AVGRETURN_THREEYEAR AS CHG_RAT_3Y, c.F_AVGRETURN_FIVEYEAR AS CHG_RAT_5Y, '' AS CHG_RAT_10Y, c.F_AVGRETURN_SINCEFOUND AS CHG_RAT_BGN, c.F_SFRANK_DAY AS CHG_RAT_1D_RK, c.F_SFRANK_RECENTWEEK AS CHG_RAT_1W_RK, c.F_SFRANK_RECENTMONTH AS CHG_RAT_1M_RK, c.F_SFRANK_RECENTQUARTER AS CHG_RAT_3M_RK, c.F_SFRANK_RECENTHALFYEAR AS CHG_RAT_6M_RK, c.F_SFRANK_RECENTYEAR AS CHG_RAT_1Y_RK, c.F_SFRANK_RECENTTWOYEAR AS CHG_RAT_2Y_RK, c.F_SFRANK_RECENTTHREEYEAR AS CHG_RAT_3Y_RK, c.F_SFRANK_RECENTFIVEYEAR AS CHG_RAT_5Y_RK, '' AS CHG_RAT_10Y_RK , c.F_SFRANK_SINCEFOUND AS CHG_RAT_BGN_RK, d.F_INCOME_PER_MILLION AS UNIT_YLD, d.F_INFO_YEARLYROE AS ANN_YLD_RAT FROM (SELECT base.*,redm.PUR_ST_temp,redm.REDEM_ST_temp FROM (SELECT CASE wb.fundtype WHEN '10901' THEN '1' ELSE '0' END AS isQDII , F_INFO_CUSTODIANBANK,f_Pchredm_Pchstartdate,f_Info_Redmstartdate,wb.companyid,wb.companyname,wb.fundfullname,wb.fundname,wb.fundtype,wb.FUND_CODE,wb.SECU_ID FROM wb_fundinfo wb , wind_db.ChinaMutualFundDescription cmfd WHERE cmfd.F_INFO_WINDCODE = wb.secu_id) base LEFT JOIN (SELECT S_INFO_WINDCODE, GROUP_CONCAT(DISTINCT F_INFO_SUSPCHTYPE) AS PUR_ST_temp, GROUP_CONCAT(DISTINCT F_INFO_SUSPCHTYPE) AS REDEM_ST_temp FROM wind_db.ChinaMutualFundSuspendPchRedm WHERE S_INFO_WINDCODE = #{trdCode} AND ( (f_info_suspchstartdt <= DATE_FORMAT(NOW(), '%Y%m%d') AND f_info_repchdt IS NULL ) OR (f_info_suspchstartdt <= DATE_FORMAT(NOW(), '%Y%m%d') AND f_info_repchdt > DATE_FORMAT(NOW(), '%Y%m%d') ) ) GROUP BY S_INFO_WINDCODE) redm ON base.SECU_ID = redm.S_INFO_WINDCODE) b LEFT JOIN wind_db.ChinaMFPerformance_new c ON b.secu_id=c.S_INFO_WINDCODE LEFT JOIN ( SELECT F_NAV_UNIT,F_INFO_WINDCODE,ANN_DATE,price_date FROM wind_db.ChinaMutualFundNAV_new cmfnav ) nav ON b.secu_id=nav.F_INFO_WINDCODE LEFT JOIN wind_db.CMoneyMarketDailyFIncome d ON b.secu_id=d.S_INFO_WINDCODE AND d.f_info_enddate =nav.price_date WHERE b.fund_code = #{trdCode} ]]> </select> </select>
    32 回复  |  直到 2019-03-12 11:13:25 +08:00
        1
    mortonnex   40 天前
    explain 走一波
        2
    leonme   40 天前 via Android   ♥ 1
    先从上层理解业务,然后在看具体 sql ~不理解业务,直接看 sql 是很痛苦的,而且还看不明白~其实这 sql 语句只是长一点,分解一下就懂了
        3
    ghbaqi   40 天前
    @leonme 好的ヽ( ̄▽ ̄)و
        4
    sonyxperia   40 天前
    2l +1
        5
    ChiangDi   40 天前
    跑路
        6
    MINYAN   40 天前
    2L+2,建议格式化以后看~
        7
    cydleadingx   40 天前
    2L+3 建议找人讲讲,从需求业务触发
        8
    ghbaqi   40 天前
    @MINYAN @cydleadingx 领导和我说之前这里外包做的 , 现在公司里没有一个人知道这块业务 ,也没有文档 。 要我主导 领导和我说 : “ 这块因为是从 0 到 1 的,公司没人了解,所以这次是希望你更多的主导的,后面这块业务你梳理好后,这块改动都要你来主导 ” 。。。。。。。
        9
    kamal   40 天前   ♥ 2
    SELECT
    b.secu_id AS f_secu_id,
    b.fund_code AS trd_code,
    b.fundfullname AS chi_name,
    b.fundname AS secu_sht,
    b.companyid AS fmc_com_id,
    b.companyname AS fmc_com_name,
    b.f_info_custodianbank AS cust_com_name,
    IF (
    f_pchredm_pchstartdate IS NULL
    OR f_pchredm_pchstartdate > Date_format(Now(), '%Y%m%d')
    OR Locate('633001001', pur_st_temp) > 0,
    '暂停申购',
    IF(
    Locate('633001002', pur_st_temp) > 0,
    '暂停大额申购',
    IF(
    Locate('633001003', pur_st_temp) > 0,
    '暂停定期定额申购',
    IF(
    Locate('633001004', pur_st_temp) > 0,
    '暂停大额定期定额申购',
    '开放申购'
    )
    )
    )
    ) AS pur_st,
    IF (
    f_info_redmstartdate IS NULL
    OR f_info_redmstartdate > Date_format(Now(), '%Y%m%d')
    OR Locate('633001005', redem_st_temp) > 0,
    '暂停赎回',
    IF(
    Locate('633001006', pur_st_temp) > 0,
    '暂停实时赎回',
    '开放赎回'
    )
    ) AS redem_st,
    b.isqdii AS is_qdii,
    b.fundtype AS inv_typ_com,
    (
    CASE
    b.fundtype
    WHEN '10100' THEN '股票型'
    WHEN '10200' THEN '债券型'
    WHEN '10300' THEN '货币型'
    WHEN '10400' THEN '混合型'
    WHEN '10600' THEN 'FOF'
    WHEN '10700' THEN '保本型'
    WHEN '10800' THEN '理财型'
    WHEN '10901' THEN 'QDII'
    WHEN '10905' THEN '指数型'
    ELSE '专户'
    end
    ) AS inv_typ_com_desc,
    Date_format(c.trade_dt, '%Y-%m-%d') AS trd_dt,
    nav.f_nav_unit AS unit_nav,
    c.f_avgreturn_day AS chg_rat_1d,
    c.f_avgreturn_week AS chg_rat_1w,
    c.f_avgreturn_month AS chg_rat_1m,
    c.f_avgreturn_quarter AS chg_rat_3m,
    c.f_avgreturn_halfyear AS chg_rat_6m,
    c.f_avgreturn_year AS chg_rat_1y,
    c.f_avgreturn_twoyea AS chg_rat_2y,
    c.f_avgreturn_threeyear AS chg_rat_3y,
    c.f_avgreturn_fiveyear AS chg_rat_5y,
    '' AS chg_rat_10y,
    c.f_avgreturn_sincefound AS chg_rat_bgn,
    c.f_sfrank_day AS chg_rat_1d_rk,
    c.f_sfrank_recentweek AS chg_rat_1w_rk,
    c.f_sfrank_recentmonth AS chg_rat_1m_rk,
    c.f_sfrank_recentquarter AS chg_rat_3m_rk,
    c.f_sfrank_recenthalfyear AS chg_rat_6m_rk,
    c.f_sfrank_recentyear AS chg_rat_1y_rk,
    c.f_sfrank_recenttwoyear AS chg_rat_2y_rk,
    c.f_sfrank_recentthreeyear AS chg_rat_3y_rk,
    c.f_sfrank_recentfiveyear AS chg_rat_5y_rk,
    '' AS chg_rat_10y_rk,
    c.f_sfrank_sincefound AS chg_rat_bgn_rk,
    d.f_income_per_million AS unit_yld,
    d.f_info_yearlyroe AS ann_yld_rat
    FROM
    (
    SELECT
    base.*,
    redm.pur_st_temp,
    redm.redem_st_temp
    FROM
    (
    SELECT
    CASE
    wb.fundtype
    WHEN '10901' THEN '1'
    ELSE '0'
    end AS isqdii,
    f_info_custodianbank,
    f_pchredm_pchstartdate,
    f_info_redmstartdate,
    wb.companyid,
    wb.companyname,
    wb.fundfullname,
    wb.fundname,
    wb.fundtype,
    wb.fund_code,
    wb.secu_id
    FROM
    wb_fundinfo wb,
    wind_db.chinamutualfunddescription cmfd
    WHERE
    cmfd.f_info_windcode = wb.secu_id
    ) base
    LEFT JOIN (
    SELECT
    s_info_windcode,
    Group_concat(DISTINCT f_info_suspchtype) AS pur_st_temp,
    Group_concat(DISTINCT f_info_suspchtype) AS redem_st_temp
    FROM
    wind_db.chinamutualfundsuspendpchredm
    WHERE
    s_info_windcode = #{trdcode}
    AND (
    (
    f_info_suspchstartdt <= date_format(now(), '%Y%m%d')
    AND f_info_repchdt IS NULL
    )
    OR (
    f_info_suspchstartdt <= date_format(now(), '%Y%m%d')
    AND f_info_repchdt > date_format(now(), '%Y%m%d')
    )
    )
    GROUP BY
    s_info_windcode
    ) redm ON base.secu_id = redm.s_info_windcode
    ) b
    LEFT JOIN wind_db.chinamfperformance_new c ON b.secu_id = c.s_info_windcode
    LEFT JOIN (
    SELECT
    f_nav_unit,
    f_info_windcode,
    ann_date,
    price_date
    FROM
    wind_db.chinamutualfundnav_new cmfnav
    ) nav ON b.secu_id = nav.f_info_windcode
    LEFT JOIN wind_db.cmoneymarketdailyfincome d ON b.secu_id = d.s_info_windcode
    AND d.f_info_enddate = nav.price_date
    WHERE
    b.fund_code = #{trdcode}
        10
    tiedan   40 天前
    我还见过上千行的存储过程
        11
    Shynoob   40 天前
    先明白这条 sql 的目的是什么,然后拆分子查询,把子查询都阅读明白,结合业务就比较好读懂了
        12
    sfz97308   40 天前
    见过比这还长好多的 SQL,来自印度...
        13
    reus   40 天前
    哪个公司啊?绝对不能在这里买基金啊!
        14
    wps353   40 天前
    这怕是属于 OLAP 的 SQL 了吧。。
        15
    MINYAN   40 天前
    @ghbaqi 哈哈哈,跟我刚进现在公司的情况一样
        16
    Raisu   40 天前 via Android
    可怕
        17
    pan569673372   40 天前 via Android
    @tiedan 我还写过上千行的嘞,理解业务万行都好说
        18
    lazyfighter   40 天前
    这是报表吧 还好 报表 sql 都很复杂
        19
    lichungang   40 天前
    印度。。哈哈哈哈,为啥这么想笑
        20
    laidycy   40 天前
    恩,不是我们公司的 SQL
        21
    ghbaqi   40 天前
    @lazyfighter 不是报表 业务代码
        22
    saulshao   40 天前
    这....厉害!这种代码貌似很多.....
    我反正见过很多很多
        23
    e2c   40 天前
    存储过程写长点无所谓,但是一个查询语句搞这么长,是要整死接手的人吗
        24
    Leigg   40 天前 via iPhone
    有毒
        25
    shehuizhuyi   40 天前 via iPhone
    @kamal 这条 sql 执行后服务器不得爆炸
        26
    kangzai50136   40 天前 via Android
    厉害。
        27
    alakey1989   40 天前
    我尼玛~1
        28
    greed1is9good   39 天前 via Android
    还好,不是很长。。。
        29
    huobazi   39 天前
    正常了
        30
    akatquas   39 天前 via iPhone
    用你的语言习惯改写业务接口,同时读代码,理解 SQL 在干嘛就很容易。
    顺便你完成了一次重构(狗头
        31
    ghbaqi   39 天前
    @akatquas 关键就是不知道业务 , 公司也没人知道这块业务 , 要我自己看 ...................
        32
    Damon4V   39 天前
    重构吧
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2210 人在线   最高记录 4385   ·  
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 20ms · UTC 12:21 · PVG 20:21 · LAX 05:21 · JFK 08:21
    ♥ Do have faith in what you're doing.
    沪ICP备16043287号-1