开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜