代码之家 › 专栏 › 技术社区 › user2589230

多个select语句上的mysql连接语法错误

statements join select mysql

1

user2589230 · 技术社区 · 10 年前

我有两个我想加入的精选语句。另外,它们工作正常,mysql告诉我我有语法错误,我不知道在哪里。查询1是:

select * from(
select items.hostid, trends_uint.itemid,
avg(trends_uint.`value_avg`)/1024/1024/1024 as Average_Used, clock,
date_format(from_unixtime(trends_uint.`clock`), '%Y-%m') AS report_date from trends_uint, items
where (trends_uint.itemid = 75283  and items.hostid=10222) 
group by trends_uint.itemid, report_date) 
as used; 

+--------+--------+-----------------------+------------+-------------+
| hostid | itemid | Average_Used          | clock      | report_date |
+--------+--------+-----------------------+------------+-------------+
|  10222 |  75283 | 1764.8172729810664676 | 1403344800 | 2014-06     |
|  10222 |  75283 | 1792.1519809950560109 | 1404190800 | 2014-07     |
+--------+--------+-----------------------+------------+-------------+

查询2是:

select * from (select items.hostid, trends_uint.itemid,
avg(trends_uint.`value_avg`)/1024/1024/1024 as Space_Allocated, clock,
date_format(from_unixtime(trends_uint.`clock`), '%Y-%m') AS report_date from trends_uint, items
where (trends_uint.itemid = 75281  and items.hostid=10222) 
group by trends_uint.itemid, report_date) as allocated;

+--------+--------+-----------------------+------------+-------------+
| hostid | itemid | Space_Allocated       | clock      | report_date |
+--------+--------+-----------------------+------------+-------------+
|  10222 |  75281 | 2432.0000000000000000 | 1403344800 | 2014-06     |
|  10222 |  75281 | 2432.0000000000000000 | 1404190800 | 2014-07     |
+--------+--------+-----------------------+------------+-------------+

我尝试以以下身份加入:

select * from(
select items.hostid, trends_uint.itemid,
avg(trends_uint.`value_avg`)/1024/1024/1024 as Average_Used, clock,
date_format(from_unixtime(trends_uint.`clock`), '%Y-%m') AS report_date from trends_uint, items
where (trends_uint.itemid = 75283  and items.hostid=10222) 
group by trends_uint.itemid, report_date) 
as used

join

select * from (select items.hostid, trends_uint.itemid,
avg(trends_uint.`value_avg`)/1024/1024/1024 as Space_Allocated, clock,
date_format(from_unixtime(trends_uint.`clock`), '%Y-%m') AS report_date from trends_uint, items
where (trends_uint.itemid = 75281  and items.hostid=10222) 
group by trends_uint.itemid, report_date) as allocated
on allocated.report_date=used.report_date;

2 回复 | 直到 10 年前

1

0

Bill Karwin 10 年前

语法错误是您有两个 SELECT 外部查询中的关键字。

您的查询格式如下:

SELECT * FROM (SELECT ...) AS used
JOIN SELECT * FROM (SELECT ...) AS allocated ON ...

相反,它需要像这样:

SELECT * FROM (SELECT ...) AS used
JOIN (SELECT ...) AS allocated ON ...

这样想: JOIN 是表引用,而不是 选择 .

2

0

catalinetu 10 年前

我想你错过了一个括号

SELECT * 
  FROM
  (
    SELECT items.hostid, trends_uint.itemid,
    AVG(trends_uint.`value_avg`)/1024/1024/1024 AS Average_Used, clock,
    DATE_FORMAT(FROM_UNIXTIME(trends_uint.`clock`), '%Y-%m') AS report_date 
    FROM trends_uint, items
    WHERE (trends_uint.itemid = 75283  AND items.hostid=10222) 
    GROUP BY trends_uint.itemid, report_date
    ) AS used
  JOIN
  (
    SELECT * FROM (SELECT items.hostid, trends_uint.itemid,
    AVG(trends_uint.`value_avg`)/1024/1024/1024 AS Space_Allocated, clock,
    DATE_FORMAT(FROM_UNIXTIME(trends_uint.`clock`), '%Y-%m') AS report_date 
    FROM trends_uint, items
    WHERE (trends_uint.itemid = 75281  AND items.hostid=10222) 
    GROUP BY trends_uint.itemid, report_date
    ) AS allocated
ON allocated.report_date=used.report_date;