snowflake sql -横向平坦函数与sql server语言 - Split_String()比较

Question

我有任务来比较计数，看看它是否>0

snowflake SQL

select 'abc'
from tableA
where 
(SELECT count(T1.value) as val
from tableA,
lateral flatten(input=>split(abc, ',')) T1
INNER JOIN (select count(T2.value) as val from 
lateral flatten(input=>split('1,2', ','))) as T2 on T1.val = T2.val) > 0

这里是错误信息

SQL compilation error: error line 7 at position 29 invalid identifier 'T2.VALUE'

Sql server比较工作

(select Count(T1.value) as [Count] from STRING_SPLIT(abc,',') AS T1
INNER JOIN STRING_SPLIT('9,10', ',') AS T2 on T1.value = T2.value))>0

Answer 1

你可能需要像这样重新定位-

select 'abc'
from tableA
where
(select count(*) from
(SELECT count(flat_tab_alias1.value) as val
from tableA,
lateral flatten(input=>split(abc, ',')) flat_tab_alias1
) as T1
INNER JOIN
(
select count(flat_tab_alias2.value) as val from
lateral flatten(input=>split('1,2', ',')) flat_tab_alias2
) as T2
on T1.val = T2.val) > 0;

<表类>"ABC">

你的SQL有很多问题，但鉴于我们有工作的T-SQL，让我们解码。

(select Count(T1.value) as [Count] from STRING_SPLIT(abc,',') AS T1
INNER JOIN STRING_SPLIT('9,10', ',') AS T2 on T1.value = T2.value))>0

这段代码拆分了一个字符串数组，并将其连接到一组"已知的"字符串中。值，并计算平均值是否相等，并询问是否有重叠。

ARRAY_OVERLAP回答了这个问题，并使用SPLIT来创建数组:

SELECT abc, 
split(abc, ',') as a1,
split('1,2', ',') as a2,
ARRAYS_OVERLAP( a1, a2)
FROM values
('1'),
('0,4'),
('0,1,5')
t(abc);

给:

<表类>ABCA1A2ARRAYS_OVERLAP (A1, A2)tbody><<tr>1("1";["1"， "2"真正]0, 4("0","4";["1"， "2")假0 1 5("0","1","5";["1"， "2"真正]