伯爵在Table2_arrays
问题描述:
Table1_IDs我有两个表的工作:伯爵在Table2_arrays
CREATE TABLE Table1
(
id int,
name varchar
)
CREATE TABLE Table2
(
id int,
name varchar,
link array<int>
)
Table2.link包含对应Table1.id值。我想统计每个Table1.id出现在Table2.link的实例中的次数。这在Excel中使用单元格引用很简单,但我无法弄清楚如何使用SQL查询来完成它。
答
普雷斯托
select *
from (select l.id
,count(*) as cnt
from Table2 cross join unnest (link) as l(id)
group by l.id
) t2
where t2.id in (select id from Table1)
order by id
presto:default> select *
-> from (select l.id
-> ,count(*) as cnt
-> from Table2 cross join unnest (link) as l(id)
-> group by l.id
-> ) t2
-> where t2.id in (select id from Table1)
-> order by id;
id | cnt
----+-----
1 | 7
2 | 5
3 | 4
(3 rows)
PostgreSQL的演示
create table Table1 (id int);
create table Table2 (arr int[]);
insert into Table1 values
(1),(2),(3)
;
insert into Table2 values
(array[1,5]),(array[1,3]),(array[1,2,3]),(array[2,3])
,(array[1,2,4]),(array[1,2]),(array[1,3,5]),(array[1,2,4])
;
select *
from (select unnest(arr) as id
,count(*) as cnt
from Table2
group by id
) t2
where t2.id in (select id from Table1)
order by id
+----+-----+
| id | cnt |
+----+-----+
| 1 | 7 |
+----+-----+
| 2 | 5 |
+----+-----+
| 3 | 4 |
+----+-----+
+0
请检查Presto版本 –
+0
Presto代码已经过验证 –
+0
这样做。非常感谢,非常感谢帮助 –
“数组”是什么意思?这不是原生的SQL数据类型。 –
@ luke.samuel.mccarthy我建议在这里放一些更详细的表格描述,或DDL描述表格的样子。这将有助于理解问题。 – Kamil
你可以请张贴一些样品的两个表 – Tajinder