mysql - 连接表并进行计数操作-MySQL

我有那些桌子：

   Members
   ---------------------------
   MemberID | Name |.....
      1
      2
      3
      4
   ---------------------------

   RentedMovies
   ---------------------------
   MemberID | MovieID | DateOfLease | ReturnDate | .....
     1      |    1    |  2012-12-20 |  2013-01-05
     1      |    2    |  2012-12-15 |  2012-12-30
     1      |    3    |  2012-12-16 |  2013-01-06
     2      |    1    |  2012-12-17 |  2012-12-18
     2      |    4    |  2012-12-18 |  2013-01-05
     3      |    1    |  2012-12-19 |  2013-01-04

   I need to get this:
   --------------------------------------------------------
   MemberID | NumberOfRentedMovies | ReturnData < curdate())
     1      |         3            |      1
     2      |         2            |      1
     3      |         1            |      0
     4      |         0            |      0
   ---------------------------------------------------------

我使用了下一个代码：

   SELECT  Members.MemberID,
    COUNT(rented.MemberID) AS NumberOfRentedMovies,
    COUNT(notTakenBackOnTime.idClana) AS NumberOfMoviesLate
   FROM  Members
   left JOIN RentedMovies as rented ON  rented.MemberID = Members.MemberID
   left JOIN RentedMovies as notTakenBackOnTime ON notTakenBackOnTime.MemberID
       = Members.MemberID AND notTakenBackOnTime.ReturnDate< CURDATE()
   group by Members.MemberID

但它不起作用！
我也试过：

   SELECT MemberID,my,my2
   FROM Members as mem
   JOIN (SELECT COUNT(* )AS my FROM RentedMovies) b
      ON b.MemberID = mem.MemberID
   JOIN (SELECT COUNT(* )AS my2 FROM RentedMovies WHERE ReturnDate< CURDATE()) c
   ON c.MemberID = mem.MemberID

但是我犯了一些错误！
所以问题是如何完成正确的解决方案？

最佳答案

你很亲密。试试这个：

SELECT  M.MemberID,
        COUNT(RM.MemberID) NumberOfRentedMovies,
        SUM(CASE WHEN RM.ReturnDate < CURDATE() THEN 1 ELSE 0 END) ReturnData
FROM Members M
LEFT JOIN RentedMovies RM
    ON M.MemberID = RM.MemberID
GROUP BY M.MemberID

关于mysql - 连接表并进行计数操作-MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14125647/