PEEWEE 結(jié)合查詢的一些筆記 窗口函數(shù)DENSE_RANK ，CASE 以及子查詢的融合

原生的SQL如圖示：

with tmp_table as (
    SELECT
        *
        DENSE_RANK () OVER ( ORDER BY "t1"."total_score" DESC ) AS "paiming",
        CASE student_name
        WHEN 'xxxxxxxxx' THEN
            '0'
        ELSE
            '1'
    END as "local_id"

    FROM
        "answer" AS "t1"
        LEFT OUTER JOIN "paper" AS "t2" ON ( "t2"."num" = "t1"."paper_num" ) 
    WHERE
        (
            ( ( ( "t1"."paper_num" = '125899331' ) AND ( "t1"."school" = '長沙廣益中學' ) ) AND ( "t1"."classs" = '0143班' ) ) 
        )
)
select * from tmp_table where student_name='xxxxxxxxx'

PEEWEE

生產(chǎn)定義用于子查詢的臨時表
              # 子查詢的條件的
              def curr_expression():
                # 默認查詢出沒做刪除的記錄
                exprsopm = (Answer.paper_num == examination_num)

                if studentname:
                    # 沒有匹配指定名稱和參數(shù)類型的操作符.您也許需要增加明確的類型轉(zhuǎn)換.
                    exprsopm = (exprsopm & (Answer.student_name == studentname)) if exprsopm else (Answer.student_name == studentname)

                if schoolname:
                    # 沒有匹配指定名稱和參數(shù)類型的操作符.您也許需要增加明確的類型轉(zhuǎn)換.
                    exprsopm = (exprsopm & (Answer.school == schoolname)) if exprsopm else (Answer.school == schoolname)

                if classsname:
                    # 沒有匹配指定名稱和參數(shù)類型的操作符.您也許需要增加明確的類型轉(zhuǎn)換.
                    exprsopm = (exprsopm & (Answer.classs == classsname)) if exprsopm else (Answer.classs == classsname)
                # 這里的擴號很關(guān)鍵(Answer.student_name == tokenname)
                # return (exprsopm) | (Answer.student_name == tokenname)
                return exprsopm



             # Case 表達式的應用
            local_id_tag = Case(None, ((Answer.student_name == tokenname, '0'),), '1')
            # dense_rank()窗口函數(shù)的應用
            paimingtag =fn.dense_rank().over(order_by=[Answer.total_score.desc()]).alias("paiming")
            subquery= (Answer.select(Answer.id,
                                         local_id_tag.alias('local_id'),
                                         Answer.student_name,
                                         Answer.student_no,
                                         Answer.total_score.alias('answer_total_score'),
                                         Answer.school.alias('answer_school'),
                                         Answer.grade.alias('answer_grade'),
                                         Answer.classs.alias('answer_class'),
                                         Answer.record_time.alias('answer_record_time'),
                                         Paper.num.alias('paper_num'),
                                         Paper.subject.alias('paper_subject'),
                                         Paper.total_score.alias('paper_total_score'),
                                         Paper.name.alias('paper_name'),
                                         Paper.grade.alias('paper_grade'),
                                         Paper.is_imitate.alias('paper_is_imitate'),
                                         paimingtag)) \
                .join(Paper,JOIN.LEFT_OUTER,on=(Paper.num==Answer.paper_num))\

再進行子查詢的處理的時候，注意的事項點，需要使用C函數(shù)魔法，錯誤的示例為：


image.png
 subquery = subquery.alias('subquery')

       
_result_sql=subquery.select(subquery.c.student_name,subquery.c.student_no).from_(subquery).where(subquery.c.student_name==tokenname)

這樣的解析的成的SQL為：
SELECT
    "t1"."student_name",
    "t1"."student_no" 
FROM
    (
    SELECT
        "t1"."id",
    CASE
            
            WHEN ( "t1"."student_name" = 'admin' ) THEN
            '0' ELSE'1' 
        END AS "local_id",
        "t1"."student_name",
        "t1"."student_no",
        "t1"."total_score" AS "answer_total_score",
        "t1"."school" AS "answer_school",
        "t1"."grade" AS "answer_grade",
        "t1"."classs" AS "answer_class",
        "t1"."record_time" AS "answer_record_time",
        "t2"."num" AS "paper_num",
        "t2"."subject" AS "paper_subject",
        "t2"."total_score" AS "paper_total_score",
        "t2"."name" AS "paper_name",
        "t2"."grade" AS "paper_grade",
        "t2"."is_imitate" AS "paper_is_imitate",
        DENSE_RANK ( ) OVER ( ORDER BY "t1"."total_score" DESC ) AS "paiming" 
    FROM
        "answer" AS "t1"
        LEFT OUTER JOIN "paper" AS "t2" ON ( "t2"."num" = "t1"."paper_num" ) 
    ) AS "subquery" 
WHERE
    ( "t1"."student_name" = 'admin' )



錯誤的原因是：應該是使用subquery的條件去查詢
正確的應該是：

SELECT
    "subquery"."student_name",
    "subquery"."student_no" 
FROM
    (
    SELECT
        "t1"."id",
    CASE
            
            WHEN ( "t1"."student_name" = 'admin' ) THEN
            '0' ELSE'1' 
        END AS "local_id",
        "t1"."student_name",
        "t1"."student_no",
        "t1"."total_score" AS "answer_total_score",
        "t1"."school" AS "answer_school",
        "t1"."grade" AS "answer_grade",
        "t1"."classs" AS "answer_class",
        "t1"."record_time" AS "answer_record_time",
        "t2"."num" AS "paper_num",
        "t2"."subject" AS "paper_subject",
        "t2"."total_score" AS "paper_total_score",
        "t2"."name" AS "paper_name",
        "t2"."grade" AS "paper_grade",
        "t2"."is_imitate" AS "paper_is_imitate",
        DENSE_RANK ( ) OVER ( ORDER BY "t1"."total_score" DESC ) AS "paiming" 
    FROM
        "answer" AS "t1"
        LEFT OUTER JOIN "paper" AS "t2" ON ( "t2"."num" = "t1"."paper_num" ) 
    ) AS "subquery" 
WHERE
    ( "subquery"."student_name" = 'admin' )

所以需要修改子查詢的條件的處理：

PS：注意識相點，子查詢的條件的.wherr必須的放到form_中，不然的會傳入的最后面的where

錯誤示例如：

SELECT 
FROM
    (
    SELECT
        "t1"."id",
    CASE
            
            WHEN ( "t1"."student_name" = 'admin' ) THEN
            '0' ELSE'1' 
        END AS "local_id",
        "t1"."student_name",
        "t1"."student_no",
        "t1"."total_score" AS "answer_total_score",
        "t1"."school" AS "answer_school",
        "t1"."grade" AS "answer_grade",
        "t1"."classs" AS "answer_class",
        "t1"."record_time" AS "answer_record_time",
        "t2"."num" AS "paper_num",
        "t2"."subject" AS "paper_subject",
        "t2"."total_score" AS "paper_total_score",
        "t2"."name" AS "paper_name",
        "t2"."grade" AS "paper_grade",
        "t2"."is_imitate" AS "paper_is_imitate",
        DENSE_RANK ( ) OVER ( ORDER BY "t1"."total_score" DESC ) AS "paiming" 
    FROM
        "answer" AS "t1"
        LEFT OUTER JOIN "paper" AS "t2" ON ( "t2"."num" = "t1"."paper_num" ) 
    WHERE
        ( ( ( "t1"."paper_num" = '125899331' ) AND ( "t1"."school" = '長沙廣益中學' ) ) AND ( "t1"."classs" = '0143班' ) ) 
    ) AS "subquery" 
WHERE
    (
        ( ( ( "t1"."paper_num" = '125899331' ) AND ( "t1"."school" = '長沙廣益中學' ) ) AND ( "t1"."classs" = '0143班' ) ) 
    AND ( "subquery"."student_name" = 'admin' ) 
    )

正確的應該是把他放在的.where必須的放到form_中j.

查詢部分字段：
   _result_sql = subquery.select(subquery.c.student_name,subquery.c.student_no).from_(subquery.where(curr_expression())).where(subquery.c.student_name==tokenname)

查詢?nèi)孔侄危?  _result_sql = subquery.select(SQL("*")).from_(subquery.where(curr_expression())).where(subquery.c.student_name==tokenname)

最終獲取到的SQL：

SELECT
    * 
FROM
    (
    SELECT
        "t1"."id",
    CASE
            
            WHEN ( "t1"."student_name" = 'admin' ) THEN
            '0' ELSE'1' 
        END AS "local_id",
        "t1"."student_name",
        "t1"."student_no",
        "t1"."total_score" AS "answer_total_score",
        "t1"."school" AS "answer_school",
        "t1"."grade" AS "answer_grade",
        "t1"."classs" AS "answer_class",
        "t1"."record_time" AS "answer_record_time",
        "t2"."num" AS "paper_num",
        "t2"."subject" AS "paper_subject",
        "t2"."total_score" AS "paper_total_score",
        "t2"."name" AS "paper_name",
        "t2"."grade" AS "paper_grade",
        "t2"."is_imitate" AS "paper_is_imitate",
        DENSE_RANK ( ) OVER ( ORDER BY "t1"."total_score" DESC ) AS "paiming" 
    FROM
        "answer" AS "t1"
        LEFT OUTER JOIN "paper" AS "t2" ON ( "t2"."num" = "t1"."paper_num" ) 
    WHERE
        ( ( ( "t1"."paper_num" = '125899331' ) AND ( "t1"."school" = '長沙廣益中學' ) ) AND ( "t1"."classs" = '0143班' ) ) 
    ) AS "subquery" 
WHERE
    ( "subquery"."student_name" = 'admin' )

完整的還原筆記：
原SQL語句：

with tmp_table as (
    SELECT
        "t1"."id",
        "t1"."student_name",
        "t1"."student_no",
        "t1"."total_score" AS "answer_total_score",
        "t1"."school" AS "answer_school",
        "t1"."grade" AS "answer_grade",
        "t1"."classs" AS "answer_class",
        "t1"."record_time" AS "answer_record_time",
        "t2"."num" AS "paper_num",
        "t2"."subject" AS "paper_subject",
        "t2"."total_score" AS "paper_total_score",
        "t2"."name" AS "paper_name",
        "t2"."grade" AS "paper_grade",
        "t2"."is_imitate" AS "paper_is_imitate",
        DENSE_RANK () OVER ( ORDER BY "t1"."total_score" DESC ) AS "paiming",
        CASE student_name
        WHEN '徐娜' THEN
            '0'
        ELSE
            '1'
    END as "local_id"

    FROM
        "answer" AS "t1"
        LEFT OUTER JOIN "paper" AS "t2" ON ( "t2"."num" = "t1"."paper_num" ) 
    WHERE
        (
            ( ( ( "t1"."paper_num" = '125899331' ) AND ( "t1"."school" = '長沙廣益中學' ) ) AND ( "t1"."classs" = '0143班' ) ) 
        )
)
select * from tmp_table where student_name='徐娜'
UNION
select * from(select * from tmp_table LIMIT 9 OFFSET 10)t ORDER BY "local_id","paiming" asc

ORM最終生成：

    def curr_expression():
                # 默認查詢出沒做刪除的記錄
                exprsopm = (Answer.paper_num == examination_num)

                if studentname:
                    # 沒有匹配指定名稱和參數(shù)類型的操作符.您也許需要增加明確的類型轉(zhuǎn)換.
                    exprsopm = (exprsopm & (Answer.student_name == studentname)) if exprsopm else (Answer.student_name == studentname)

                if schoolname:
                    # 沒有匹配指定名稱和參數(shù)類型的操作符.您也許需要增加明確的類型轉(zhuǎn)換.
                    exprsopm = (exprsopm & (Answer.school == schoolname)) if exprsopm else (Answer.school == schoolname)

                if classsname:
                    # 沒有匹配指定名稱和參數(shù)類型的操作符.您也許需要增加明確的類型轉(zhuǎn)換.
                    exprsopm = (exprsopm & (Answer.classs == classsname)) if exprsopm else (Answer.classs == classsname)
                # 這里的擴號很關(guān)鍵(Answer.student_name == tokenname)
                # return (exprsopm) | (Answer.student_name == tokenname)
                return exprsopm


            local_id_tag = Case(None, ((Answer.student_name == tokenname, '0'),), '1')
            paimingtag =fn.dense_rank().over(order_by=[Answer.total_score.desc()]).alias("paiming")
            subquery= (Answer.select(Answer.id,
                                         local_id_tag.alias('local_id'),
                                         Answer.student_name,
                                         Answer.student_no,
                                         Answer.total_score.alias('answer_total_score'),
                                         Answer.school.alias('answer_school'),
                                         Answer.grade.alias('answer_grade'),
                                         Answer.classs.alias('answer_class'),
                                         Answer.record_time.alias('answer_record_time'),
                                         Paper.num.alias('paper_num'),
                                         Paper.subject.alias('paper_subject'),
                                         Paper.total_score.alias('paper_total_score'),
                                         Paper.name.alias('paper_name'),
                                         Paper.grade.alias('paper_grade'),
                                         Paper.is_imitate.alias('paper_is_imitate'),
                                         paimingtag)) \
                .join(Paper,JOIN.LEFT_OUTER,on=(Paper.num==Answer.paper_num))\


            # 子查詢里面的
            # 進行結(jié)果的模型的充命名處理
            subquery = subquery.alias('subquery')

            subquery_expression = subquery.where(curr_expression())
            # 注意點c魔法函數(shù)的使用
            queryoen = subquery.select(SQL("*")).from_(subquery_expression)

            query1 =queryoen.where(subquery.c.student_name==tokenname)

            query_all_subquery = queryoen.alias('all_subquery')

            # query2 = query_all_subquery.select(SQL("*")).from_(query_all_subquery.paginate(pageNo, pageSize)).order_by(query_all_subquery.c.local_id.asc(),query_all_subquery.c.paiming.asc())
            query2 = query_all_subquery.select(SQL("*")).from_(query_all_subquery.paginate(pageNo, pageSize)).order_by(SQL("local_id ,paiming asc"))
            # union_result = (query1) | query2

            union_result = query1.union_all(query2)

union注意事項點：

 union_result = query1 | query2 UNION 
第一種形式會把 前后兩個SELECT進行使用（）進行擴起來
union_result = query1.union_all(query2)
第二種形式，不會進行
所以總的區(qū)分開就是：UNION ALL

SQL使用點：

  query2 = query_all_subquery.select(SQL("*")).from_(query_all_subquery.paginate(pageNo, pageSize)).order_by(SQL("local_id ,paiming asc"))
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

友情鏈接更多精彩內(nèi)容