mysql - sql GROUP BY 产生语法错误

Question

我无法理解这个查询有什么问题。它不断产生语法错误。非常感谢。

SELECT 
    `employees`.`ID`, `employees`.`LNAME`, `employees`.`FNAME`, `employees`.`MINIT`, 
    `leave_app`.`ID`, `leave_app`.`CONTROL_NUM`, `leave_app`.`DATE_FILED`,
    `leave_app`.`DATE_FROM`, `leave_app`.`DATE_TO`, `leave_app`.`TYPE`, 
    `leave_app`.`EMP_FK`
FROM employees
LEFT JOIN `trackr_pgso`.`leave_app` ON `employees`.`ID` = `leave_app`.`EMP_FK`
ORDER BY `employees`.`ID` ASC
GROUP BY `employees`.`ID`
LIMIT 0 , 30

我有这样的东西:

1|  6/7/20141|  6/30/20141|  7/1/20142|  6/15/20143|  6/29/2014

我想要的是这样的:

1|  6/7/2014    6/30/2014    7/1/20142|  6/15/20143|  6/29/2014

Answer 1

您必须将 order by 子句放在 group by 子句之后:

SELECT `employees`.`ID` , `employees`.`LNAME` , `employees`.`FNAME` , `employees`.`MINIT` , `leave_app`.`ID` , `leave_app`.`CONTROL_NUM` , `leave_app`.`DATE_FILED` , `leave_app`.`DATE_FROM` , `leave_app`.`DATE_TO` , `leave_app`.`TYPE` , `leave_app`.`EMP_FK`
FROM employees
LEFT JOIN `trackr_pgso`.`leave_app` ON `employees`.`ID` = `leave_app`.`EMP_FK`
GROUP BY `employees`.`ID`
ORDER BY `employees`.`ID` ASC
LIMIT 0 , 30

您必须先分组，然后订购。

这是来自 Mysql documentation 的语法:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]