MariaDB社区

标题: 查询语句求助 [打印本页]

作者: lingjia75 时间: 2010-7-30 10:04
标题: 查询语句求助
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 时间: 2010-7-30 15:38
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`;

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

欢迎光临 MariaDB社区 (http://123.56.88.72/)