# 67 subquery實現復雜查詢 ## 子查詢 子查詢可以讓多個查詢變成一個查詢，只要查找一次數據庫，性能相對來講更加高效一點，不用寫多個sql語句就可以一些復雜的查詢了，那么在sqlalchemy中，要實現一個子查詢，應該使用以下幾個步驟: 1.將子查詢按照傳統的方式寫好查詢代碼，然后在"query"對象后面執行"subquery"方法，將這個查詢變成一個子查詢 ```text user = session.query(func.max(User.age).label("age")).subquery() ``` 2.在子查詢中，將以后需要用到的字段通過"label"方法，取個別名 ```text session.query(func.max(User.age).label("age")) ``` 3.在父查詢中，如果想要使用子查詢的字段，那么可以通過子查詢的變量上的"c"屬性拿到 ```text result = session.query(User).filter(User.age == user.c.age).all() ``` ## 完整代碼 ```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) city = Column(String(50),nullable=False) age = Column(Integer,default=0) def __repr__(self): return "<User(username:%s)>" % self.username # Base.metadata.drop_all() # Base.metadata.create_all() # # user1 = User(username="A",city="M城",age=15) # user2 = User(username="B",city="G城",age=21) # user3 = User(username="C",city="H城",age=12) # user4 = User(username="D",city="J城",age=20) # # session.add_all([user1,user2,user3,user4]) # session.commit() # # mysql> select * from user where age = 21; user = session.query(func.max(User.age).label("age")).subquery() result = session.query(User).filter(User.age == user.c.age).all() print(result) # # 實現子查詢 # subuser = session.query(User.city.label("city"),User.age.label("age")).filter(User.username == 'A').subquery() # # subquery.column.city == subquery.c.city # result = session.query(User).filter(User.city == subuser.c.city).all() # print(result) ```