php - Mysql:选择指定点之间的所有数据

Question

我有一个 mysql 表，其中的数据连接到点。像这样:

tbl_user

tbl_earned_points

tbl_used_points

我有一个 mysql 查询，用于选择可用点的数据。这是查询:

SELECT
user.user_email_id AS userEmailId,
IFNULL(SUM(earn_points.earned_points),0) AS lifeTimePoints,
IFNULL((SUM(earn_points.earned_points) - (SELECT IFNULL(SUM(used_points.points_used),0) FROM tbl_used_points AS used_points WHERE used_points.user_id=earn_points.user_id)),0) AS availablePoints
FROM tbl_earned_points AS earn_points
RIGHT JOIN tbl_user AS user ON earn_points.user_id=user.user_id WHERE user.user_email_id <> '' AND user.user_email_id <> '0'
GROUP BY user.user_email_id
ORDER BY availablePoints ASC

从上面的查询中，我收到了所有电子邮件和结果，例如

userEmailId              lifeTimePoints     availablePoints
samal07@gmail.com        1745               1500
mistyorr_20@yahoo.com    100                75
bsfar@yahoo.com          85                 85
tgray@gmail.com          94                 90
lori_bag@yahoo.com       547                450

我的问题是我只需要获取可用点在 80 到 99 之间的行。

所以我的查询结果如下

 userEmailId        lifeTimePoints      availablePoints
 bsfar@yahoo.com        85              85
 tgray@gmail.com        94              90

Answer 1

只需将其添加到 WHERE 子句中即可:

AND availablePoints BETWEEN 80 AND 99