查询语句求助

lingjia75

mysql 版本：
Server version: 5.1.45-community MySQL Community Server (GPL)
相关表结构：
mysql> desc alarm_site;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| ALARM_id        | int(10)          | NO   | PRI | NULL    | auto_increment |
| ALARM_def_ID    | int(10) unsigned | NO   |     | NULL    |                |
| ALARM_date      | date             | NO   |     | NULL    |                |
| ALARM_severity  | char(20)         | YES  |     | NULL    |                |
| ALARM_starttime | char(20)         | YES  |     | NULL    |                |
| ALARM_cleartime | char(20)         | YES  |     | NULL    |                |
| ALARM_status    | char(32)         | YES  |     | NULL    |                |
| ALARM_comments  | varchar(512)     | YES  |     | NULL    |                |
| ALARM_component | char(50)         | YES  |     | NULL    |                |
| ALARM_count     | int(10)          | NO   |     | NULL    |                |
| Site_id         | int(10)          | NO   |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

mysql> desc alarm_def;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra
|
+---------------------+------------------+------+-----+---------+----------------+
| ALARM_def_ID        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ALARM_def_Function  | varchar(512)     | NO   |     | NULL    | |
| ALARM_def_IMR       | char(128)        | YES  |     | NULL    | |
| ALARM_def_Fixed     | char(128)        | YES  |     | NULL    | |
| ALARM_def_Comments1 | text             | YES  |     | NULL    | |
| ALARM_def_Comments2 | text             | YES  |     | NULL    | |
| User_ID             | int(10)          | YES  |     | NULL    | |
+---------------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc site_def;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| Site_id   | int(10)  | NO   | PRI | NULL    |       |
| Site_name | char(48) | NO   |     | NULL    |       |
| Site_load | char(96) | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.06 sec)

下面的查询没有问题，可以运行（注意加粗部分）
select `alarm_site`.`ALARM_def_ID` AS `ALARM_def_ID`,`alarm_site`.`Site_id` AS `Site_id`,`alarm_site`.`ALARM_date` AS `ALARM_date`,group_concat(`alarm_site`.`ALARM_component` separator ',') AS `Component`,group_concat(distinct `alarm_site`.`ALARM_severity` separator ',') AS `Severity`,group_concat(`alarm_site`.`ALARM_starttime`,_utf8'~',`alarm_site`.`ALARM_cleartime` separator ',') AS `Fire_Clear_time`,`alarm_def`.`ALARM_def_Function` AS `ALARM_def_Function`,`site_def`.`Site_name` AS `Site_name`
from ((`alarm_site` join `alarm_def` on((`alarm_site`.`ALARM_def_ID` = `alarm_def`.`ALARM_def_ID`))) join `site_def` on((`alarm_site`.`Site_id` = `site_def`.`Site_id`)))
where (`alarm_site`.`ALARM_date` = (select max(`alarm_site`.`ALARM_date`) AS `max(alarm_date)` from `alarm_site`))
group by `alarm_site`.`ALARM_def_ID`,`alarm_site`.`Site_id`,`alarm_site`.`ALARM_date`

将黑体部分修改成以下格式出错：
select `alarm_site`.`ALARM_def_ID` AS `ALARM_def_ID`,`alarm_site`.`Site_id` AS `Site_id`,`alarm_site`.`ALARM_date` AS `ALARM_date`,group_concat(`alarm_site`.`ALARM_component` separator ',') AS `Component`,group_concat(distinct `alarm_site`.`ALARM_severity` separator ',') AS `Severity`,group_concat(`alarm_site`.`ALARM_starttime`,_utf8'~',`alarm_site`.`ALARM_cleartime` separator ',') AS `Fire_Clear_time`,`alarm_def`.`ALARM_def_Function` AS `ALARM_def_Function`,`site_def`.`Site_name` AS `Site_name`
from ((`alarm_site` join `alarm_def` on((`alarm_site`.`ALARM_def_ID` = `alarm_def`.`ALARM_def_ID`))) join `site_def` on((`alarm_site`.`Site_id` = `site_def`.`Site_id`)))
where (`alarm_site`.`ALARM_date` in  (select DISTINCT `ALARM_date`  from `alarm_site` order by alarm_date desc limit 30 )
group by `alarm_site`.`ALARM_def_ID`,`alarm_site`.`Site_id`,`alarm_site`.`ALARM_date`

[Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

查了一下关于Err 1235的解决方案，修改为以下语句：
select `alarm_site`.`ALARM_def_ID` AS `ALARM_def_ID`,`alarm_site`.`Site_id` AS `Site_id`,`alarm_site`.`ALARM_date` AS `ALARM_date`,group_concat(`alarm_site`.`ALARM_component` separator ',') AS `Component`,group_concat(distinct `alarm_site`.`ALARM_severity` separator ',') AS `Severity`,group_concat(`alarm_site`.`ALARM_starttime`,_utf8'~',`alarm_site`.`ALARM_cleartime` separator ',') AS `Fire_Clear_time`,`alarm_def`.`ALARM_def_Function` AS `ALARM_def_Function`,`site_def`.`Site_name` AS `Site_name`
from ((`alarm_site` join `alarm_def` on((`alarm_site`.`ALARM_def_ID` = `alarm_def`.`ALARM_def_ID`))) join `site_def` on((`alarm_site`.`Site_id` = `site_def`.`Site_id`)))
where (`alarm_site`.`ALARM_date` in (select * from  (select DISTINCT `ALARM_date`  from `alarm_site` order by alarm_date desc limit 30 ) as t2)
group by `alarm_site`.`ALARM_def_ID`,`alarm_site`.`Site_id`,`alarm_site`.`ALARM_date`;

出错如下：


[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by `alarm_site`.`ALARM_def_ID`,`alarm_site`.`Site_id`,`alarm_site`.`ALARM_' at line 1

求高手帮忙。。。拜谢。。。

je_ck

select `alarm_site`.`ALARM_def_ID` AS `ALARM_def_ID`,`alarm_site`.`Site_id` AS `Site_id`,`alarm_site`.`ALARM_date` AS `ALARM_date`,group_concat(`alarm_site`.`ALARM_component` separator ',') AS `Component`,group_concat(distinct `alarm_site`.`ALARM_severity` separator ',') AS `Severity`,group_concat(`alarm_site`.`ALARM_starttime`,_utf8'~',`alarm_site`.`ALARM_cleartime` separator ',') AS `Fire_Clear_time`,`alarm_def`.`ALARM_def_Function` AS `ALARM_def_Function`,`site_def`.`Site_name` AS `Site_name`
from ((`alarm_site` join `alarm_def` on((`alarm_site`.`ALARM_def_ID` = `alarm_def`.`ALARM_def_ID`))) join `site_def` on((`alarm_site`.`Site_id` = `site_def`.`Site_id`)))
where (`alarm_site`.`ALARM_date` in (select * from  (select DISTINCT `ALARM_date`  from `alarm_site` order by alarm_date desc limit 30 ) as t2) )
group by `alarm_site`.`ALARM_def_ID`,`alarm_site`.`Site_id`,`alarm_site`.`ALARM_date`;

少一个括号！！！！，我用黑体给你标注出来了