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

Question

我有 2 个我想加入的选择语句。单独地，它们工作正常，一起 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;

Answer 1

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

您的查询形式如下:

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

相反，它需要像这样:

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

这样想:JOIN 的操作数是表引用，而不是 SELECT 的实例。