# 65 group\_by和having子句 ## group\_by 根據某個字段進行分組 ## having: having是對查找結果進一步過濾，在分組的基礎上在進行篩選過濾 ## 子查詢 sqlalchemy支持子查詢，比如現在要查找一個用戶的用戶名以及該用戶的郵箱地址數量。要滿足這個需求，可以在子查詢中查找到所有用戶的郵箱數（通過group by合并同一用戶），然后再將結果放在父查詢中進行使用: ```text result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age < 18).all() print(result) ``` ```text from sqlalchemy import create_engine,Column,String,ForeignKey,Float,DateTime,Date,Integer,Enum,func from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker,relationship,backref from datetime import datetime HOSTNAME = "127.0.0.1" PORT = "3306" USERNAME = "root" PASSWORD = "123456" DATABASE = "xt_flask" DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE) engine = create_engine(DB_URI) Base = declarative_base(engine) session = sessionmaker(engine)() class User(Base): __tablename__ = "user" id = Column(Integer,primary_key=True,autoincrement=True) username = Column(String(50),nullable=False) age = Column(Integer,default=0) gender = Column(Enum("male","female","secret")) def __repr__(self): return "<Article(id:%s,username:%s)>" % (self.id.self.username) # Base.metadata.drop_all() # Base.metadata.create_all() # # user1 = User(username="angle",age=17,gender="male") # user2 = User(username="miku",age=18,gender="male") # user3 = User(username="xue",age=18,gender="female") # user4 = User(username="yue",age=19,gender="female") # user5 = User(username="mi",age=20,gender="female") # # session.add_all([user1,user2,user3,user4,user5]) # session.commit() # 每個年齡段的人數 # select count(*) from user group by age; from sqlalchemy.orm.query import Query # result = session.query(User.age,func.count(User.id)).group_by(User.age).all() result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age < 18).all() print(result) ```