多个查询在同一列上的位置

I have a database of customers and would like to run a query to display how many customers have an active account on a month by month basis.

我有一个客户数据库,并希望运行查询以显示每月有多少客户拥有活动帐户。

I already have the following to count how many customers have an expiry date for each month, but can anybody suggest a way to then count those with an expirydate >= now for each month?

我已经有以下数据来计算每个月有多少客户有一个到期日期,但是有人可以建议一种方法来计算每个月的expirydate> = now吗?

<?php 
$query = "select count(cid) as num_rows from customers WHERE expirydate LIKE '%-12-%'";
$result = mysql_query($query); 
$row = mysql_fetch_object( $result ); 
$total = $row->num_rows; 
echo "December: " .$total; 
?>

3 个解决方案

#1

Simple

mysql_query( "select count(cid) as num_rows from customers WHERE expirydate >= curdate()" );

Try to avoid mysql_* functions due to they are depricated.Instead use mysqli_* functions or PDO statements

尽量避免使用mysql_ *函数,因为它们已被删除。相反使用mysqli_ *函数或PDO语句

#2

The following will tell you have many customers have an expirydate per month for records on or after today.

以下内容将告诉您,许多客户每月都有一份到期日期的记录。

SELECT 
    MONTH(expirydate) month, COUNT(cid) as count 
FROM 
    customers 
WHERE 
    DATE(expirydate) >= CURDATE() 
GROUP BY MONTH(expirydate)

Do note though that as this groups only by month, records spanning multiple years may be misleading (3 July 2013 and 7 Jul 2014 would both count as July, for example).

请注意,虽然这个组仅按月分组,但跨越多年的记录可能会产生误导(例如,2013年7月3日和2014年7月7日都将计为7月)。

You should look into the date and time functions and group by/aggregate functions for more information.

您应该查看日期和时间函数以及分组/聚合函数以获取更多信息。

#3

This query show you how much customers has expirydate after now. Correct your date format in STR_TO_DATE function for your case. MySQL STR_TO_DATE

此查询显示了此后客户已过期的数量。在STR_TO_DATE函数中更正您的日期格式。 MySQL STR_TO_DATE

SELECT COUNT(*) as `num_rows`, MONTH(STR_TO_DATE(`expirydate`,'%Y-%m-%d')) as `month`
FROM `customers`
WHERE STR_TO_DATE(`expirydate`,'%Y-%m-%d') > NOW()
GROUP BY `month`

Also try to use timestamp field type in your tables instead of varchar or etc.

还尝试在表中使用timestamp字段类型而不是varchar等。

#1