对前几行的条件插入
对前几行的条件插入
提问于 2021-04-27 09:59:08
我有一个players表，定义如下：
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('players_id_seq'::regclass)
player_name | character varying(64) | | not null |
server_name | character varying(64) | | not null |
joined_at | timestamp without time zone | | not null |它有以下数据：
id | player_name | server_name | joined_at
----+-------------+--------------+----------------------------
1 | Jane | blue-server | 2021-04-27 10:42:51.986282
2 | Matt | red-server | 2021-04-27 10:42:51.986282
3 | Jane | green-server | 2021-04-27 10:42:51.986282
4 | Jane | blue-server | 2021-04-27 10:42:51.986282我创建了一个大小提琴。
所需的查询
我想编写一个insert，它的成功取决于player_name和server_name值。如果为给定的player_name和server_name组合插入的最后一行匹配，那么插入应该失败，否则插入应该继续。
因此，在上面，下面的插入应该会失败
insert into players (player_name, server_name, joined_at) values ('Matt', 'red-server', now());因为player_name = 'Matt'的最后一条记录也有相同的server_name = 'red-server'
但是，如果继承的话，这两种情况都会发生。
insert into players (player_name, server_name, joined_at) values ('Matt', 'grey-server', now());
insert into players (player_name, server_name, joined_at) values ('Matt', 'red-server', now());我希望这是有意义的。这是另一个例子。下面的应该会使失败
insert into players (player_name, server_name, joined_at) values ('Jane', 'blue-server', now());我不知道如何在SQL中实现这一点。
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-04-27 10:18:10
创建一个返回要插入的列值的CTE，并使用CTE中的INSERT ... SELECT和一个WHERE子句而不是INSERT ... VALUES。
WITH cte(player_name, server_name, joined_at) AS (SELECT 'Matt', 'red-server', now())
INSERT INTO players (player_name, server_name, joined_at)
SELECT c.*
FROM cte c
WHERE c.server_name <>
COALESCE((SELECT p.server_name FROM players p WHERE p.player_name = c.player_name ORDER BY joined_at DESC LIMIT 1), '')见演示。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67280886
复制相关文章
相似问题
腾讯云开发者
