php - 执行查询时出现问题(不在 GROUP BY 中)

Question

我正在使用 PHP 并尝试在 MySQL 上执行查询，

当我使用 MySqlYog 执行此查询时，我得到了结果，一切似乎都正常。

查询:

SELECT 
  start_time AS `Date`,
  COUNT(1) AS `Count` 
FROM
  offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY
AND application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT'
GROUP BY (WEEK(start_time))
ORDER BY `Date` ASC ;

但问题是当我试图从 PHP 执行查询时:

我收到这个错误:

Invalid query: 'nolio_db.offline_execution_jobs.start_time' isn't in GROUP BY

如果有人遇到过这个问题，我会很高兴听到如何克服它？

我需要 GROUP BY 的输出格式如下:

第一列为 DATE 格式的一周的第一天，第二列为事件计数。

2011-01-09 03:28:54 | 38

Answer 1

如果您按 start_time 的 WEEK 分组，那么这就是您应该选择的内容(而不是日期本身)。否则，您将如何报告计数？您想要显示每周的计数，而不是每个日期的计数。

您的 where 子句中也存在语义错误 - 您应该使用括号明确设置 AND 和 OR 的优先顺序。

SELECT WEEK(start_time) AS Week, COUNT(1) AS Count 
FROM offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY 
AND (application_name LIKE 'SPLAT-ROLLING' 
OR application_name LIKE 'SPLAT' )
GROUP BY WEEK(start_time)
ORDER BY WEEK(start_time) ASC ;

这个查询:

SELECT WEEK(start_time) AS Week, COUNT(1) AS Count 
FROM offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY 
AND (application_name LIKE 'SPLAT-ROLLING' 
OR application_name LIKE 'SPLAT' )
GROUP BY WEEK(start_time)
ORDER BY WEEK(start_time) ASC ;

返回以下结果:

1 21
2 50
3 15

但是我需要这样的东西:

2011-01-04 08:05:24  21
2011-01-09 03:28:54  8
2011-01-16 06:08:18  11
2011-01-23 06:06:50  32

当我从 MySqlYog(MySql windows 客户端)执行这个查询时，我得到了想要的结果，当我从 php 代码执行这个查询时出现了问题:

SELECT start_time AS WEEK, COUNT(1) AS COUNT 
FROM offline_execution_jobs 
WHERE start_time >= NOW() - INTERVAL 365 DAY 
AND (application_name LIKE 'SPLAT-ROLLING' 
OR application_name LIKE 'SPLAT' )
GROUP BY WEEK(start_time)
ORDER BY WEEK(start_time) ASC ;

这是我从 php 得到的错误:

Invalid query: 'nolio_db.offline_execution_jobs.start_time' isn't in GROUP BY

Whole query: SELECT start_time AS Date, COUNT(1) AS Count FROM offline_execution_jobs WHERE start_time >= NOW() - INTERVAL 250 DAY AND (application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT' ) GROUP BY WEEK(start_time) ORDER BY WEEK(start_time) ASC ;

这就是代码的样子:

$query =    "SELECT start_time AS Date, COUNT(1) AS Count 
                    FROM offline_execution_jobs 
                    WHERE start_time >= NOW() - INTERVAL 250 DAY 
                    AND (application_name LIKE 'SPLAT-ROLLING' OR application_name LIKE 'SPLAT' )
                    GROUP BY WEEK(start_time)
                    ORDER BY WEEK(start_time) ASC ;";

    //echo "<br><br>$query<br><br>";

    // Create connection to DB                              
    $conn = mysql_connect($db_host, $db_user, $dp_pass);

    if (!$conn) 
    {   
        echo "<br/>Can't connect: $db_host";
        die('Could not connect: ' . mysql_error());
    }