我有下表:
>>> id crop grower loc
0 11 maize Lulu Fiksi
1 13 maize Lulu Menter
2 05 maize Felix Hausbauch
3 04 apples Lulu Fiksi
4 02 apples Meni Linter
5 06 cotton Delina Marchi
6 12 cotton Lexi Tinta
7 16 cotton Lexi Ferta
...
我希望创建新的表,该表将显示唯一的作物名称、作物外观计数,然后列出种植这种作物的所有种植者,因此结果表应该如下所示:
>>> crop total_count growers
0 maize 3 Lulu, Felix
1 apples 2 Lulu,Meni
2 cotton 3 Delina, Lexi
我设法创建了一个表,该表显示作物和总数,但没有种植者的名称:
select "CROP",count(*) "totalCount"
from "table"
group by "CROP"
order by "totalCount" desc
我的问题是如何创建新的表,其中包含每种作物的唯一种植者列表(如本例所示)。
发布于 2021-11-16 11:24:36
GROUP_CONCAT用于MySQL,雪花使用LISTAGG:
create or replace table test (
id int,
crop varchar,
grower varchar,
loc varchar
);
insert into test values
(11, 'maize', 'Lulu', 'Fiksi'),
(13, 'maize', 'Lulu', 'Menter'),
(5, 'maize', 'Felix', 'Hausbauch'),
(4, 'apples', 'Lulu', 'Fiksi'),
(2, 'apples', 'Meni', 'Linter'),
(6, 'cotton', 'Delina', 'Marchi'),
(12, 'cotton', 'Lexi', 'Tinta'),
(16, 'cotton', 'Lexi', 'Ferta');
select
crop,
count(1) as total_count,
listagg(distinct grower, ', ') as growers
from test
group by crop
;
+--------+-------------+--------------+
| CROP | TOTAL_COUNT | GROWERS |
|--------+-------------+--------------|
| maize | 3 | Lulu, Felix |
| apples | 2 | Lulu, Meni |
| cotton | 3 | Delina, Lexi |
+--------+-------------+--------------+
发布于 2021-11-16 09:40:12
您可以根据您的数据库使用GROUP_CONCAT()或任何相关的乐趣
select "CROP",count(*) "totalCount",GROUP_CONCAT(grower) as growers
from "table"
group by "CROP"
order by "totalCount" desc
https://stackoverflow.com/questions/69986448
复制相似问题