SQL Query that relies on COUNT from another table? - SQLAlchemy
I need a query that can return the开发者_开发百科 records from table A that have greater than COUNT records in table B. The query needs to be able to go in line with other filters that might be applied on table A.
Example case study:
I have a person and appointments table. I am looking for all people who have been in for 5 or more appointments. It must also support extra filter statements on the person table, such as age > 18.
EDIT -- SOLUTION
subquery = db.session.query(Appointment.id_person,
func.count('*').label('person_count')) \
.group_by(Appointment.id_person).subquery()
qry = db.session.query(Person) \
.outerjoin((subquery, Person.id == subquery.c.id_person)) \
.order_by(Person.id).filter(subquery.c.person_count >= 5).filter(Person.dob <= '1992-10-29')
Use a subquery:
SELECT * from person
WHERE PersonID IN
(SELECT PersonId FROM appointments
GROUP BY PersonId
HAVING COUNT(*) >= 5)
AND dob > 25
SELECT Person.PersonID, Person.Name
FROM Person INNER JOIN Appointment
ON Person.PersonID = Appointment.PersonID
GROUP BY Person.PersonID, Person.Name
HAVING COUNT(Person.PersonID) >= 5
精彩评论