php - MySQL单个查询计算多个日期范围内的行数

Question

我必须计算 mysql 表中多个日期范围(今天、周、月)的行数。

目前，我正在 PHP 脚本中运行一个循环，并对每个日期范围进行单独的查询，我知道这是一个坏主意。

<?php
$now = "2016-04-21";
$today = $now;
$week = date( 'Y-m-d', strtotime( '-7 day', strtotime( $now ) ) );
$month = substr( $now, 0, 7 );

$res1 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND DATE_FORMAT( date, '%Y-%m-%d' ) = '$today'" );
$res2 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND ( t.date BETWEEN ( '$week' ) AND ( '$today' ) )");
$res3 = mysql_query("SELECT SUM(pageview) FROM statistics WHERE user = '$id' AND DATE_FORMAT( t.date, '%Y-%m' ) = '$month'" );

所以我想使用单个查询来执行此操作。

我找到了count rows in multiple date ranges和 query for grabbing multiple date ranges但我不确定哪个是最好的，以及在我的情况下该怎么做

Answer 1

如果您确实想使用一个查询检索三个值:

$res = mysql_query("
SELECT sum1, sum2, sum3 
FROM (SELECT SUM(pageview) AS sum1 FROM statistics WHERE user = '$id' AND DATE_FORMAT( date, '%Y-%m-%d' ) = '$today') AS T1
JOIN (SELECT SUM(pageview) AS sum2 FROM statistics WHERE user = '$id' AND ( t.date BETWEEN ( '$week' ) AND ( '$today' ) )) AS T2
JOIN (SELECT SUM(pageview) AS sum3 FROM statistics WHERE user = '$id' AND DATE_FORMAT( t.date, '%Y-%m' ) = '$month') AS T3");

虽然这并没有带来更好的性能，但我确实没有理由这样做。

附言像这样将变量插入到查询中是一个坏习惯。在这种情况下你是安全的，但如果你使用用户输入的变量，你就会受到 SQL 注入(inject)的影响。尝试学习prepared statements