我正在开发一个系统,应该输出在一天内发送的短信总数。它还应将交付的和失败的分开。它应该只输出那天的记录..。就像那天的记录一样。我的数据库是smsdb。
$sql = "CREATE TABLE smsdb (
id INT(6) AUTO_INCREMENT PRIMARY KEY,
sms_text VARCHAR(300) NOT NULL,
receiver_number VARCHAR(30) NOT NULL,
time_sent DATETIME,
status VARCHAR(20) )";
//I tried to use the command to display todays records but I get all records even for other days.
$sql = ("SELECT DATE(time_sent) AS date, COUNT(*) as total_sent, SUM(status = 'Success') as delivered,SUM(status = 'failed') AS failed FROM smsdb GROUP BY DATE(time_sent) ");我只想要今天的总记录。好样的,谁来帮我
发布于 2016-06-10 14:50:41
只需筛选当前日期的where标准:
SELECT
DATE(time_sent) AS date,
COUNT(*) as total_sent,
SUM(case when status = 'Success' then 1 else 0 end) as delivered,
SUM(case when status = 'failed' then 1 else 0 end) AS failed
FROM smsdb
WHERE date(time_sent)=curdate()
GROUP BY DATE(time_sent)https://stackoverflow.com/questions/37750975
复制相似问题