mysql - MySQL选择在表中具有超过“列值”记录的成员

我准备了下列表格：

MEMBERS

_________________________________________________
|memberid  |   firstname  | lastname  | sessions|
_________________________________________________
|1         |   john       | doe       | 1        |
|2         |  Dave        | Smith     | 2        |
_________________________________________________

BOOKINGS
___________________________________
|id    |memberid  |   date    | time  | group |
___________________________________
|1     |1         |2016-04-19 | 08:00 | 1     |
|2     |2         |2016-04-18 | 13:00 | 4     |
|3     |1         |2016-04-15 | 18:00 | 3     |
_______________________________________________

我想返回本周预订表中有超过members.sessions记录的所有成员的名字和姓氏。
我有生成这两个日期的php代码
$FirstDay=日期（“Y-m-d”，strotime（“上星期日”））；
$LastDay=日期（“Y-m-d”，strtotime（“本周星期日”））；
替换为where子句=where bookings.date介于'$FirstDay'和'$LastDay'之间
非常感谢您的帮助！

最佳答案

这应该可以做到：

SELECT firstname, lastname, sessions, count(bookings.id) AS sum_bookings
FROM members
JOIN bookings ON bookings.memberid = members.memberid
WHERE date BETWEEN ? AND ?
GROUP BY members.memberid
HAVING sessions > sum_bookings

阅读有关BETWEENhere的更多信息
或者用你的变量：

$query = "SELECT firstname, lastname, sessions, count(bookings.id) AS sum_bookings
FROM members
JOIN bookings ON bookings.memberid = members.memberid
WHERE date BETWEEN '".$FirstDay."' AND '".$LastDay."'
GROUP BY members.memberid
HAVING sessions > sum_bookings";

在插入这样的变量时要小心。它可能使您容易受到SQL注入的攻击，最好使用prepared statements

关于mysql - MySQL选择在表中具有超过“列值”记录的成员，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36719314/