原生的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"))
网友评论