Hive where int/string ＜＞ int/int/string ＜＞ string cast类型转化/where自动过滤掉NULL，保留‘‘-蒲公英云

Hive where int/string ＜＞ int/int/string ＜＞ string cast类型转化/where自动过滤掉NULL，保留‘‘

Dear 丶 2021-09-23 05:00 376阅读 0赞

1 int/string <> int，会把string cast成 int ,不能转化为int的字符串都会cast成NULL，<>int也会把NULL值的记录过滤掉，’’不是NULL故’’保留在结果中

select cast('' as int) --res7，结果是NULL
select cast(' ' as int) --res8，结果是NULL 
select cast(null as int) -- 结果是NULL 
select cast('1' as int)  --结果是1          
select cast('' as string) --结果是''
select *
from (
select 1 as c1
union all
select 2 as c1
union all
select '34' as c1
union all
select '3ab4' as c1
union all
select 'ab' as c1
union all
select '' as c1
union all
select ' ' as c1
union all
select null as c1
) t1  where c1 <> 1 ; --res15，2行记录分别是2，34，不能转化为int的字符串都会cast成NULL，<>1会把NULL过滤掉

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70

select *
from (
select '1' as c1
union all
select '2' as c1
union all
select '' as c1
union all
select ' ' as c1
union all
select null as c1
) t1  where c1 <> 1 ;  --res10,1行记录是2

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70 1

select *
from (
select 1 as c1
union all
select 2 as c1
union all
select '' as c1
union all
select ' ' as c1
union all
select null as c1
) t1  where c1 <> 1 ; --res5,1行记录是2

" class="reference-link">

#

2 int/string <> string，会把int cast成string，<>string也会把NULL值的记录过滤掉，’’不是NULL故’’保留在结果中

select *
from (
select 1 as c1
union all
select 2 as c1
union all
select '34' as c1
union all
select '3ab4' as c1
union all
select 'ab' as c1
union all
select '' as c1
union all
select ' ' as c1
union all
select null as c1
) t1  where c1 <> '1'  -- res16,结果共6行，分别是2，34，3ab4,ab,'',' ',NULL值被过滤掉

" class="reference-link">

select *
from (
select 1 as c1
union all
select 2 as c1
union all
select '' as c1
union all
select ' ' as c1
union all
select null as c1
) t1  where c1 <> '1' ;    --res9,3行记录,分别是2，'',' '