如何在Snowflake(SQL)中添加指示重复id的列?

尽管这是一件很奇怪的事情,但以下是你可以做到的:

update table1
set table2_multiple = case when t.cnt > 1 then 1 else 0 end 
from (select ID , count(*) cnt from table2 group by ID) t 
where t.id = table1.id

或者,如果您只是想选择:

select t1.* , case when t2.cnt > 1 then 1 else 0 end as table2_multiple
from table1 t1 
join (select ID , count(*) cnt from table2 group by ID) t2
on t1.id = t2.id

SELECT t1.ID, t1.Data, case when count(*) > 1 then 1 else 0 end as table2_Multiple
FROM Table1 t1 --t1 is an alias of table1
LEFT JOIN table2 t2 --t2 is an alias of table2
 ON t1.ID = t2.ID
GROUP BY T1.ID, T1.Data

使用分析函数:(Count()over(partition xxx)这基本上表示按唯一的T1ID和数据对所有记录进行计数,然后表达式表示如果该计数为>1,则返回1,否则返回0。distinct然后消除所有重复项。

SELECT Distinct t1.ID
     , t1.Data
     , case when count() over (partition by T1.ID, T1.Data) > 1 then 1 else 0 end as Table_2_multiple
LEFT JOIN Table2 T2
  on T1.ID = T2.ID

使用内联视图(T2)获取表2中的计数在这种情况下,子查询将只返回每个ID的1行,因此不需要处理倍数。

SELECT T1.*, case when coalesce(t2.ValueNo,0) > 1 then 1 else 0 end as table2_Multiple 
FROM Table1
LEFT JOIN (SELECT ID, count(*) as valueNo 
           FROM Table2 
           GROUP BY ID) T2
 on T1.ID = T2.ID