PEEWEE 结合查询的一些笔记 窗口函数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

生产定义用于子查询的临时表
              # 子查询的条件的
              def curr_expression():
                # 默认查询出没做删除的记录
                exprsopm = (Answer.paper_num == examination_num)

                if studentname:
                    # 没有匹配指定名称和参数类型的操作符.您也许需要增加明确的类型转换.
                    exprsopm = (exprsopm & (Answer.student_name == studentname)) if exprsopm else (Answer.student_name == studentname)

                if schoolname:
                    # 没有匹配指定名称和参数类型的操作符.您也许需要增加明确的类型转换.
                    exprsopm = (exprsopm & (Answer.school == schoolname)) if exprsopm else (Answer.school == schoolname)

                if classsname:
                    # 没有匹配指定名称和参数类型的操作符.您也许需要增加明确的类型转换.
                    exprsopm = (exprsopm & (Answer.classs == classsname)) if exprsopm else (Answer.classs == classsname)
                # 这里的扩号很关键(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()窗口函数的应用
            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函数魔法，错误的示例为：


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)

查询全部字段：
  _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:
                    # 没有匹配指定名称和参数类型的操作符.您也许需要增加明确的类型转换.
                    exprsopm = (exprsopm & (Answer.student_name == studentname)) if exprsopm else (Answer.student_name == studentname)

                if schoolname:
                    # 没有匹配指定名称和参数类型的操作符.您也许需要增加明确的类型转换.
                    exprsopm = (exprsopm & (Answer.school == schoolname)) if exprsopm else (Answer.school == schoolname)

                if classsname:
                    # 没有匹配指定名称和参数类型的操作符.您也许需要增加明确的类型转换.
                    exprsopm = (exprsopm & (Answer.classs == classsname)) if exprsopm else (Answer.classs == classsname)
                # 这里的扩号很关键(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))\


            # 子查询里面的
            # 进行结果的模型的充命名处理
            subquery = subquery.alias('subquery')

            subquery_expression = subquery.where(curr_expression())
            # 注意点c魔法函数的使用
            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)
第二种形式，不会进行
所以总的区分开就是：UNION ALL

SQL使用点：

  query2 = query_all_subquery.select(SQL("*")).from_(query_all_subquery.paginate(pageNo, pageSize)).order_by(SQL("local_id ,paiming asc"))
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 218,284评论 6赞 506
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,115评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 164,614评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,671评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,699评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,562评论 1赞 305
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,309评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,223评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,668评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,859评论 3赞 336
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,981评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,705评论 5赞 347
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,310评论 3赞 330
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,904评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,023评论 1赞 270
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,146评论 3赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,933评论 2赞 355