当前位置：文江博客话题详情

MySQL：是否可以“如果具有特定值的行数小于 X，则插入”？

发布于 2024-08-16 22:06:00 字数 228 浏览 7 评论 0原文

打个简单的比喻，我有一个表格如下：

id(PK)| gift_giver_id（外籍）|礼物接收者 ID（外籍）| Gift_date

是否可以在单个查询中更新表，仅当该人迄今为止拥有的礼物少于 10 件（即具有相同 Gift_giver_id 的行少于 10 行）时，才会添加一行（即该人的另一份礼物））？

这样做的目的是将桌子的大小限制为每人 10 份礼物。

提前致谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鹿! 2024-08-23 22:06:00

尝试：

insert into tablename
   (gift_giver_id, gift_receiver_id, gift_date)  
select GIVER_ID, RECEIVER_ID, DATE from Dual where  
   (select count(*) from tablename where gift_receiver_id = RECEIVER_ID) < 10

try:

insert into tablename
   (gift_giver_id, gift_receiver_id, gift_date)  
select GIVER_ID, RECEIVER_ID, DATE from Dual where  
   (select count(*) from tablename where gift_receiver_id = RECEIVER_ID) < 10

回复收藏 0 原文

忘羡 2024-08-23 22:06:00

“这也是‘否则，更新最旧行中的字段’吗？”

这也是一个相当血腥的重要附录：P

我不会在单个查询中做如此复杂的事情，我会选择首先测试最旧的，然后相应地更新或插入。

由于不知道除 SQL 之外您还使用什么语言，因此我将坚持使用非 SQL 部分的伪代码。

SELECT TOP 1 id FROM gifts
WHERE (SELECT COUNT(*) FROM gifts WHERE gift_giver_id = senderidvalue
ORDER BY gift_date ASC) > 9;

{if result.row_count then}

INSERT INTO gifts (gift_giver_id, gift_receiver_id,gift_date)
VALUES val1,val2,val3

{else}

UPDATE gifts SET gift_giver_id = 'val1',
gift_receiver_id = 'val2',gift_date = 'val3'
WHERE {id = result.first_row.id}

您的请求的问题是您试图找到一个查询来执行 SELECT 以及 INSERT 或 UPDATE。很可能有人会来批评我，以证明我错了，但我认为除非您想进入存储过程，否则您是在要求不可能的事情。

"And would that also be, 'otherwise, update the fields in the oldest row'?"

And would that also be, a rather bloody significant annendum :P

I wouldn't do something that complex in a single query, I'd select first to test for the oldest and then either update or insert accordingly.

Not knowing what language you're working in other than SQL, I'll just stick to pseudocode for non-SQL portions.

SELECT TOP 1 id FROM gifts
WHERE (SELECT COUNT(*) FROM gifts WHERE gift_giver_id = senderidvalue
ORDER BY gift_date ASC) > 9;

{if result.row_count then}

INSERT INTO gifts (gift_giver_id, gift_receiver_id,gift_date)
VALUES val1,val2,val3

{else}

UPDATE gifts SET gift_giver_id = 'val1',
gift_receiver_id = 'val2',gift_date = 'val3'
WHERE {id = result.first_row.id}

The problem with your request is you're trying to find a single query to perform a SELECT as well as either an INSERT or an UPDATE. Someone may well come along and call me out on this to prove me wrong but I think you're asking for the impossible unless you want to get into stored procedures.

回复收藏 0 原文

碍人泪离人颜 2024-08-23 22:06:00

我不是 SQL 专家，但我认为类似以下的内容应该可行：（假设表名是gifts）：

INSERT INTO gifts (gift_giver_id, gift_receiver_id,gift_date)
SELECT DISTINCT senderidvalue,receiveridvalue,datevalue FROM gifts
WHERE (SELECT COUNT(*) FROM gifts WHERE gift_giver_id = senderidvalue ) < 10;

[编辑]代码格式不喜欢我:(

I'm no SQL guru but I'm thinking something like the following should work: (assuming a table name of gifts):

INSERT INTO gifts (gift_giver_id, gift_receiver_id,gift_date)
SELECT DISTINCT senderidvalue,receiveridvalue,datevalue FROM gifts
WHERE (SELECT COUNT(*) FROM gifts WHERE gift_giver_id = senderidvalue ) < 10;

[edit] Code formatting doesn't like me :(

回复收藏 0 原文

~没有更多了~