项目中左连接带同一表的二次查询

最新推荐文章于 2024-04-25 23:48:15 发布

chssheng2007

最新推荐文章于 2024-04-25 23:48:15 发布

阅读量573

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL语句文章标签： date training join user css

本文链接：https://blog.youkuaiyun.com/chssheng2007/article/details/5894887

SQL语句专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询旨在从多个表中筛选出特定状态为'7'的应聘记录，并且职位ID属于一系列预定义的ID列表。通过连接不同表来获取额外的信息，如跟踪类型和编号等，并按申请日期和申请ID进行排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select
        b.*
    from
        (select
            a.* ,
            rownum rn
        from
            (SELECT
                *
            FROM
                (SELECT
                    DISTINCT P.F_ID AS F_APPLY_ID,
                    P.F_IS_LOCKED,
                    P.F_APPLY_STATUS,
                    P.F_RECRUIT_TYPE,
                    P.F_RESUME_INTO_TYPE,
                    P.F_POST_ID,
                    P.F_APPLY_DATE,
                    P.F_POST_NAME AS F_POST_TEMP_NAME,
                    P.F_INTERVIEW_ROUND,
                    P.F_RESUME_SCORE,
                    P.F_ASSESS_SCORE,
                    P.F_SCREENING_SCORE,
                    P.F_SEND_ASSESS_MAIL_DATE,
                    P.F_SEND_ASSESS_MESSAGE_DATE,
                    P.F_SEND_EXAM_MAIL_DATE,
                    P.F_SEND_EXAM_MESSAGE_DATE,
                    P.F_ACCEPT_OFFER_DATE,
                    P.F_RESUME_ID,
                    P.F_IS_INNER_COMMEND,
                    P.F_RETEST_AUDIT_LEVEL,
                    P.F_HIRING_AUDIT_LEVEL,
                    P.F_RESUME_SOURCE,
                    P.F_PROCESS_USER_ID,
                    P.F_CHANGE_APPLY_STATUS_DATE,
                    PI.F_NAME_EN AS F_POST_NAME_EN,
                    PI.F_NAME_CH AS F_POST_NAME_CH,
                    PI.F_POST_CODE,
                    P.F_JOIN_STATUS,
                    P.F_JOB_FORWARD,
                    P.F_EVALUATE_STATUS,
                    P.F_TAKE_MATERIALS_DATE,
                    P.F_TRAINING_DATE,
                    F_TRAINING_SCENE,
                    F_ENTRY_POST_ID,
                    P.F_TRAINING_BATCH,
                    P.F_ASSIGN_USER ,
                    P.F_ASSIGN_DATE
,TK.F_TRACK_TYPE ,TK.F_TRACK_NUM,TK1.F_TRACK_NUM+10 AS T_TRACK_ALL,TK.F_ADD_DATE
                FROM
                    T_APPLY_INFO P
                LEFT JOIN
                    T_CORP_POST_INFO PI
                        ON PI.F_ID = P.F_POST_ID
,t_css_track TK,t_css_track TK1
                WHERE
                    1=1
                    AND P.F_APPLY_STATUS='7'
                    AND (
                        P.F_POST_ID = '34080002'
                        OR P.F_POST_ID = '43000005'
                        OR P.F_POST_ID = '44631499'
                        OR P.F_POST_ID = '45010489'
                        OR P.F_POST_ID = '43910004'
                        OR P.F_POST_ID = '46980005'
                        OR P.F_POST_ID = '38990007'
                        OR P.F_POST_ID = '48351601'
                        OR P.F_POST_ID = '48351728'
                        OR P.F_POST_ID = '48612055'
                        OR P.F_POST_ID = '32530004'
                        OR P.F_POST_ID = '46250167'
                        OR P.F_POST_ID = '44280002'
                        OR P.F_POST_ID = '39840002'
                        OR P.F_POST_ID = '47040009'
                        OR P.F_POST_ID = '32970002'
                        OR P.F_POST_ID = '101212'
                        OR P.F_POST_ID = '48613908'
                        OR P.F_POST_ID = '48620571'
                        OR P.F_POST_ID = '48643475'
                        OR P.F_POST_ID = '48650442'
                        OR P.F_POST_ID = '48650459'
                        OR P.F_POST_ID = '48664976'
                        OR P.F_POST_ID = '48351117'
                        OR P.F_POST_ID = '48481820'
                        OR P.F_POST_ID = '48612138'
                        OR P.F_POST_ID = '48661884'
                        OR P.F_POST_ID = '48663038'
                        OR P.F_POST_ID = '48391353'
                        OR P.F_POST_ID = '48643344'
                        OR P.F_POST_ID = '48660034'
                        OR P.F_POST_ID = '48613060'
                        OR P.F_POST_ID = '48643440'
                        OR P.F_POST_ID = '48650448'
                        OR P.F_POST_ID = '48440561'
                        OR P.F_POST_ID = '48450043'
                        OR P.F_POST_ID = '48500046'
                        OR P.F_POST_ID = '48582580'
                        OR P.F_POST_ID = '48643479'
                        OR P.F_POST_ID = '48643812'
                        OR P.F_POST_ID = '48662670'
                        OR P.F_POST_ID = '47470276'
                        OR P.F_POST_ID = '47920126'
                        OR P.F_POST_ID = '45010048'
                        OR P.F_POST_ID = '48361311'
                        OR P.F_POST_ID = '48440341'
                        OR P.F_POST_ID = '48611832'
                        OR P.F_POST_ID = '48650438'
                        OR P.F_POST_ID = '48350328'
                        OR P.F_POST_ID = '48350537'
                        OR P.F_POST_ID = '48351250'
                        OR P.F_POST_ID = '48352793'
                        OR P.F_POST_ID = '48353811'
                        OR P.F_POST_ID = '48461184'
                        OR P.F_POST_ID = '48461209'
                        OR P.F_POST_ID = '48461220'
                    )
                    AND (
                        P.F_POST_ID <> '0'
                    )
  AND TK.F_RESUME_ID = P.F_RESUME_ID AND TK1.F_RESUME_ID = P.F_RESUME_ID
    AND TK.f_Track_Type = 1 AND TK1.f_Track_Type=0
)
            ORDER BY
                NLSSORT (F_APPLY_DATE,
                'NLS_SORT=SCHINESE_PINYIN_M') desc,
                NLSSORT (F_APPLY_ID,
                'NLS_SORT=SCHINESE_PINYIN_M') desc) a
            where
                rownum <= '10') b
        where
            rn > '0'