Sqlalchemy 如何实现 case + subquery 嵌套 subquery

select case
           when user.id > 10000 then
               (select true_flag
                from score
                where created_time = (
                    select max(created_time)
                    from score
                    where user.id = score.user_id
                ))
           when user.id <= 10000 then
               (select true_flag
                from score_bak
                where created_time = (
                    select max(created_time) from score_bak where user.id = score_bak.user_id
                ) 
               ) 
        end true_flag
from user;

使用 case 函数最后会变成 select true_flag from user,score 把两个表连表了

Supplement 1 · Sep 8, 2022

真的瑞了，投降了，使用原生sql

from sqlalchemy import text,select,column
sql = text(sql)
sql = sql.columns(column('true_flag'))
subquery = select(sql.selected_columns.true_flag).select_from(sql.subquery())

3 replies • 2022-09-09 09:58:16 +08:00

xhzhang

Sep 8, 2022

这个你可以把 case 语句放到 where 的 join 条件中，用 user join 两张表，添加不同的 join 条件，select 语句就按照你现在的逻辑取数就可以

xhzhang

Sep 8, 2022

select xxx from user join score on user.id = score.user_id and user.id > 10000 join score_bak on user.id = score.user_id and user.id <= 10000

joApioVVx4M4X6Rf

Sep 9, 2022

试了一下好像还真是和楼主说的会变成连表