python SQLAlchemy中子查询subquery的使用
基本配置：
https://www.cnblogs.com/whycai/p/11963443.html
原始sql：
将表中数据按照name分组，其他字段展示最新的数据，于是，先要排序后，再进行分组
1 select a.name,count(*),a.datetime from 2 (select * from TableName where 1=1 and datetime >= "2020-1-9 11:00:00" 3 and datetime <= "2020-1-9 20:00:00" ORDER BY id DESC LIMIT 100000) a 4 GROUP BY a.name ;
将sql拆解：
1 1. 2 select * from TableName where 1=1 and datetime >= "2020-1-9 11:00:00" 3 and datetime <= "2020-1-9 20:00:00" ORDER BY id DESC LIMIT 100000 4 5 2. 6 select a.name,count(*),a.datetime from 7 (1中sql) a 8 GROUP BY a.name ;
SQLAlchemy的写法：
1 sql_text = ' 1=1 and datetime >= "2020-1-9 11:00:00" and datetime <= "2020-1-9 20:00:00"' 2 3 #相当于 sql拆解中的1 4 resSub = db.session.query(TableName).filter(text(sql_text)).order_by(TableName.id.desc()).limit(10000).subquery() 5 6 #相当于 sql拆解中的2 7 res = db.session.query(resSub.c.name, func.count(resSub.c.name), resSub.c.datetime).group_by(resSub.c.name).all()
