mysql - 如果表为空或满足连接条件则插入

Question

对于硬核 SQLers 来说是个不错的谜题。

2 个表:拍卖和出价。

CREATE TABLE auctions
(
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at INT,
timer INT DEFAULT 10,
user_id BIGINT UNSIGNED
)ENGINE=InnoDB;

CREATE TABLE bids
(
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
created_at INT,
user_id BIGINT UNSIGNED,
auction_id BIGINT UNSIGNED
)ENGINE=InnoDB; 

给定拍卖，如果满足以下条件之一，用户可以对其出价:

• 最后一次出价创建时间少于 auction.timer 秒前
• 拍卖没有出价

简而言之:这是一个在 x 秒内没有人关注的最后出价用户赢得拍卖的游戏。

此处不允许交易。

这是我的尝试:

INSERT INTO bids (user_id, auction_id) 
SELECT 1, a.id
FROM auctions AS a, bids AS b
WHERE a.id = b.auction_id
AND a.user_id IS NULL
AND a.id = 1
AND (UNIX_TIMESTAMP() - b.created_at) < a.timer
LIMIT 1

它有效，但我不知道如何将“或没有为给定的拍卖 ID 出价”逻辑放在里面。

Answer 1

如果我对你的理解正确，你需要一个外连接(可选)到出价。像这样:

INSERT INTO bids (user_id, auction_id) 
SELECT 1, a.id
FROM auctions AS a
left outer join bids AS b
ON a.id = b.auction_id
AND (UNIX_TIMESTAMP() - b.created_at) < a.timer
where
(a.user_id IS NULL
AND a.id = 1

) OR
b.id IS NULL     --this will be true if there is no bid present
LIMIT 1