我有一个包含一堆条目的数据库,其中一些条目可能如下所示:
| id | name | field1 | field2 | field3 |
----------------------------------------
| 1 | id1 | 2 | 3 | |
| 2 | id2 | 2 | 1 | |
| 3 | id1 | | | 5 |
| 4 | id2 | | | 8 |
我正在尝试运行以下SQL语句以将相似的行合并在一起,并将它们放回表中:
SELECT name, max(id), max(field1), max(field2), max(field3) INTO groups FROM groups GROUP BY name;
我希望能给我:
| id | name | field1 | field2 | field3 |
----------------------------------------
| 3 | id1 | 2 | 3 | 5 |
| 4 | id2 | 2 | 1 | 8 |
但这句话只是给了我ERROR: column "max" specified more than once
我在这里的陈述有什么问题?感谢您的任何帮助!
您使用的语法不正确,因为select ... into ...
会创建一个新表。您想要更新或删除现有表中的行。您可以使用通用表表达式来执行此操作:
with new_rows as (
select
name,
max(id) as id,
max(field1) as field1,
max(field2) as field2,
max(field3) as field3
from groups
group by name
),
deleted_rows as (
delete from groups
where id not in (
select id
from new_rows
)
)
update groups g
set field1 = n.field1, field2 = n.field2, field3 = n.field3
from new_rows n
where n.id = g.id
returning g.*;
id | name | field1 | field2 | field3
----+------+--------+--------+--------
3 | id1 | 2 | 3 | 5
4 | id2 | 2 | 1 | 8
(2 rows)
您只需要从查询中取出INTO xxx
,并为列设置别名,以便以后可以区分它们:
-- Aliasing columns
SELECT
name, max(id) AS id, max(field1) AS field1, max(field2) AS field2, max(field3) AS field3
FROM
groups
GROUP BY
name;
。你得到
名称 | ID | 字段1 | 字段 2 | 字段 3 :--- |-: |-----: |-----: |-----: ID1 | 3 | 2 | 3 | 5 ID2 | 4 | 2 | 1 | 8
在这里检查所有内容
正如klin在他/她的回答中提到的,SELECT INTO
创建一个新表,如果没有列别名,几列将被称为max
。请参阅PostgreSQL Select into