left join on between
白色广东人 发布于2021-06 浏览:2688 回复:6
0
收藏
快速回复
最后编辑于2021-06

SELECT 
DATE_ADD(t1.etta_depart_date,CAST(MIN(eag.aging_time)/24 AS BIGINT)) AS expect_arrive_time
FROM a表 t1
LEFT JOIN (
SELECT DISTINCT mileage_from,mileage_to,aging_time
FROM b表
) eag ON t1.etta_rate_mileage between eag.mileage_from and eag.mileage_to

报了SQL 错误 [1064] [42000]: errCode = 2, detailMessage = non-equal LEFT OUTER JOIN is not supported 错误,

请问一下这种left join on between有没有好的写法,他们之间只有区间的关系?

收藏
点赞
0
个赞
共6条回复 最后由Ling缪回复于2021-06
#7Ling缪回复于2021-06
#6 白色广东人回复
SELECT  DATE_ADD(t1.etta_depart_date,CAST(MIN(eag.aging_time)/24 AS BIGINT)) AS expect_arrive_time FROM a表 t1 LEFT JOIN ( SELECT DISTINCT mileage_from,mileage_to,aging_time FROM b表 ) eag ON t1.etta_rate_mileage between eag.mileage_from and eag.mileage_to 不好意思 sql重新整理一下发出来了
展开

嗯,目前如果 Join 的条件不是等值条件 是不能 Left join  ,Doris 确实还没支持。

0
#6白色广东人回复于2021-06
#2 Ling缪回复
这?你这么写的between and 的话?那 eag.mileage_from 这列的取值是??常量??还是一组数?
展开

SELECT 
DATE_ADD(t1.etta_depart_date,CAST(MIN(eag.aging_time)/24 AS BIGINT)) AS expect_arrive_time
FROM a表 t1
LEFT JOIN (
SELECT DISTINCT mileage_from,mileage_to,aging_time
FROM b表
) eag ON t1.etta_rate_mileage between eag.mileage_from and eag.mileage_to

不好意思 sql重新整理一下发出来了

0
#5白色广东人回复于2021-06

SELECT 
DATE_ADD(t1.etta_depart_date,CAST(MIN(eag.aging_time)/24 AS BIGINT)) AS expect_arrive_time
FROM a表 t1
LEFT JOIN (
SELECT DISTINCT mileage_from,mileage_to,aging_time
FROM b表
) eag ON t1.etta_rate_mileage between eag.mileage_from and eag.mileage_to

0
#4Ling缪回复于2021-06

很可能是因为你这里between  and  写的是一个表中的列名,然后 doris 判定发现这里不是一个常量所以没支持

0
#3白色广东人回复于2021-06
#2 Ling缪回复
这?你这么写的between and 的话?那 eag.mileage_from 这列的取值是??常量??还是一组数?
展开

常量来的,就是取某个时间区间的时效就是某个常量。

0
#2Ling缪回复于2021-06

这?你这么写的between and 的话?那 eag.mileage_from 这列的取值是??常量??还是一组数?

0
快速回复
TOP
切换版块