无法在配置单元中使用非相等联接(< =)

az31mfrm 于 2021-06-26 发布在 Hive

关注(0)|答案(2)|浏览(203)

我是配置单元的新手，正在尝试执行一个包含非相等联接的查询。但是当我尝试在配置单元0.7中执行时，由于非相等连接条件，我得到了一个错误。
查询：

SELECT * INTO  dbo.tbl_B
 FROM ( select 
    Id,
    Name,
    Age,
    SellingPrice,
    row_number() OVER ( PARTITION BY Id,Name
ORDER BY Age asc) AS row_no
from tbl_A
LEFT JOIN dbo.tbl_C
ON A.[Id]=C.[Id]
and A.SellingPrice  <= C.BuyingPrice
) AA
WHERE row_no = 1 ;

请提供我的想法重写这个查询而不使用非等效连接。

Hive left-join hiveql

来源：https://stackoverflow.com/questions/52121165/unable-to-use-nonequi-join-in-hive

2条答案

按热度按时间

7eumitmz1#

Solution not working for me

query used:

CREATE TABLE  IMART_PREP.TST (STRT_DT DATE , END_DT DATE, ID INT , SALARY INT );

INSERT INTO IMART_PREP.TST VALUES (   '2018-01-01' ,   '2018-01-31' ,1, 1000);
INSERT INTO IMART_PREP.TST VALUES (  '2018-02-01' , '2018-02-28' , 1, 2000);
INSERT INTO IMART_PREP.TST VALUES (  '2018-03-01' ,  '2018-03-30' , 1, 3000);
INSERT INTO IMART_PREP.TST VALUES (  '2018-04-01' ,  '2018-04-30' , 1, 4000);
SELECT * FROM IMART_PREP.TST;

CREATE TABLE  IMART_PREP.TST_1 (STRT_DT DATE , END_DT DATE, ID INT , SALARY INT );
INSERT INTO IMART_PREP.TST_1 VALUES (   '2018-01-01' ,   '2018-01-31', 1, 1000);

SELECT *
FROM IMART_PREP.TST A ,  IMART_PREP.TST_1 B
WHERE  ( (A.ID = B.ID ) AND (A.STRT_DT BETWEEN B.STRT_DT AND B.END_DT ) ) OR (B.STRT_DT IS NULL );

赞(0）回复(0）举报 2021-06-26

iqxoj9l92#

将非等联接条件移到where子句：

SELECT * 
 FROM ( select 
        Id,
        Name,
        Age,
        SellingPrice,
        row_number() OVER ( PARTITION BY Id,Name ORDER BY Age asc) AS row_no
from tbl_A
LEFT JOIN dbo.tbl_C
ON A.[Id]=C.[Id]
where (A.SellingPrice  <= C.BuyingPrice) OR (C.BuyingPrice is null)
) AA
WHERE row_no = 1 ;
``` `OR (C.BuyingPrice is null)` 是允许左连接所必需的，如果没有此条件，左连接将转换为内部连接，因为只有非相等条件会过滤掉空值，请参见此处。

赞(0）回复(0）举报 2021-06-26

我来回答

无法在配置单元中使用非相等联接(< =)

2条答案

相关问题

热门标签

最新问答