SQL查询::一个计数中的参数?

Let's say I have a table of movie renters with columns:

假设我有一个带有列的电影renter表:

• UserID
• 用户标识
• MovieID
• MovieID
• Rent_Start_date
• Rent_Start_date
• Rent_Due_Date
• Rent_Due_Date

I am trying to achieve an output table that looks like:

我正在尝试实现如下的输出表:

[UserID, Count of Movies Due in 1 Day, Count of Movies Due in 1 Week]

Is this possible to do in one single query? I currently have a php script that runs 1 query on movies due in 1 day and another query that runs movies due in 1 week. These two queries are then looped for every user ID, filling in the table essentially slot by slot. This is kind of slow.

这在一个查询中可以实现吗?我目前有一个php脚本，它对1天到期的电影运行一个查询，对1周到期的电影运行另一个查询。然后对每个用户ID对这两个查询进行循环，按槽填入表。这有点慢。

By attempting to create this output with only 1 query, I tried something like:

通过尝试创建只有1个查询的输出，我尝试了如下方法:

  SELECT UserID, count(movieID)
    FROM MovieTable
GROUP BY movieID

But this doens't create columns of counts of expiration dates.

但这不会创建过期日期计数的列。

Is it possible to create a count column that has an arguement such as count( //all those satisfy where Rent_Due_Date - CURDATE() < ONE_WEEK)?

是否可以创建一个count列，其中包含count(//所有满足Rent_Due_Date - CURDATE() < ONE_WEEK)的条目?

1 个解决方案

#1

You need to group your results by user, not by movie:

你需要将你的结果按用户分组，而不是通过电影:

SELECT   UserID,
         SUM(Rent_Due_Date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 DAY),
         COUNT(*)
FROM     MovieTable
WHERE    Rent_Due_Date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 WEEK
GROUP BY UserID