hive 两表community,onsale连接建立迎新表 community表中有ID, build_year,building_num,house_num;onsale表中有community build_area ID_Community build_year building_num house_num has_elevator,通过community的ID和onsale的ID_Community进行连接
时间: 2024-01-12 22:05:29 浏览: 24
您可以使用以下SQL语句进行连接:
```
SELECT c.ID, c.build_year, c.building_num, c.house_num, o.has_elevator
FROM community c
JOIN onsale o ON c.ID = o.ID_Community;
```
这将从两个表中选择所需的列,并使用 `JOIN` 关键字基于 `ID` 和 `ID_Community` 进行连接。
相关问题
hive 两表community,onsale连接建立迎新表 community表中有ID community build_year building_num house_num,onsale表中有ID_Onsale total_price unit_price community build_area start_sale_time focus_num ID_Community longitude latitude build_year building_num house_num has_elevator,通过community的ID和onsale的ID_Community进行连接
可以使用Hive SQL语句进行连接操作,示例代码如下:
```
SELECT c.ID, c.build_year, c.building_num, c.house_num, o.ID_Onsale, o.total_price, o.unit_price, o.community, o.build_area, o.start_sale_time, o.focus_num, o.has_elevator, c.longitude, c.latitude
FROM community c
JOIN onsale o
ON c.ID = o.ID_Community;
```
这个语句会从两个表中选取所需的列,并且通过 `JOIN` 操作将它们连接起来。连接条件是 community 表的 ID 列和 onsale 表的 ID_Community 列相等。
0: jdbc:hive2://192.168.64.182:10000> SELECT c.ID, on.community, on.build_area, c.build_year, c.building_num, c.house_num, on.has_elevator FROM onsale on JOIN Community c ON on.ID_Community = c.ID limit 6; FAILED: ParseException line 1:11 missing EOF at ',' near 'ID' Error: Error while compiling statement: FAILED: ParseException line 1:11 missing EOF at ',' near 'ID' (state=42000,code=40000)
这个错误提示表明在SQL语句的第1行第11个字符处附近出现了错误,可能是因为在这个位置附近有一个非法的字符或者缺少了一些必要的字符。具体而言,这个错误可能是由于下面的这个子句产生的:
```
SELECT c.ID, on.community, on.build_area, c.build_year, c.building_num, c.house_num, on.has_elevator FROM onsale on JOIN Community c ON on.ID_Community = c.ID limit 6;
```
这里使用了JOIN子句连接了两个表,但是在连接条件中使用了"on.ID_Community = c.ID"的语法,这个语法在Hive中是不支持的,应该使用"on.ID_Community == c.ID"的语法来代替。另外,也可以使用等价的USING子句来代替ON子句,例如:
```
SELECT c.ID, on.community, on.build_area, c.build_year, c.building_num, c.house_num, on.has_elevator
FROM onsale on
JOIN Community c USING (ID_Community)
LIMIT 6;
```
这里使用了USING子句来连接两个表,使用"ID_Community"作为连接条件,该语法在Hive中是支持的,可以正确执行查询语句了。