执行查询时出错(不在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的输出格式如下：

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

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

Answer 1

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

你在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());
    }