请求编写排除已获取项的SQL查询及随机获取非已取项的SQL语句

Hey there! Let's tackle your two SQL needs step by step with concrete examples you can adapt to your database setup.

First, let's assume you have two core tables to work with:

• items: Stores all available items (columns like item_id, name, description are typical)
• acquired_items: Tracks items that have already been fetched (might include item_id, user_id if you're tracking per-user acquisitions, or just item_id for global tracking)

Here's how to pull items that haven't been acquired yet:

-- 全局排除所有已获取过的项
SELECT i.*
FROM items i
LEFT JOIN acquired_items ai ON i.item_id = ai.item_id
WHERE ai.item_id IS NULL;

-- 针对特定用户排除其已获取的项（比如用户ID为123）
SELECT i.*
FROM items i
LEFT JOIN acquired_items ai ON i.item_id = ai.item_id AND ai.user_id = 123
WHERE ai.item_id IS NULL;

Quick breakdown #

• The LEFT JOIN matches every item in items with any corresponding entry in acquired_items.
• WHERE ai.item_id IS NULL filters out any items that have a match in the acquired table—leaving only the ones that haven't been fetched yet.
• For per-user exclusion, we add the user_id condition directly in the JOIN to avoid accidentally excluding items acquired by other users.

This builds on the first requirement, but adds a random sorting layer. The exact syntax varies slightly by database, so here are the most common versions:

MySQL/MariaDB #

-- 随机获取5个未被用户123通过随机方式获取过的项
SELECT i.*
FROM items i
LEFT JOIN acquired_items ai ON i.item_id = ai.item_id 
  AND ai.user_id = 123
  AND ai.fetch_method = 'random' -- 仅排除通过随机方式获取的项
WHERE ai.item_id IS NULL
ORDER BY RAND()
LIMIT 5; -- 调整数字为你需要的随机条数

PostgreSQL #

PostgreSQL uses RANDOM() instead of RAND():

SELECT i.*
FROM items i
LEFT JOIN acquired_items ai ON i.item_id = ai.item_id 
  AND ai.user_id = 123
  AND ai.fetch_method = 'random'
WHERE ai.item_id IS NULL
ORDER BY RANDOM()
LIMIT 5;

SQL Server #

For SQL Server, use NEWID() to generate random ordering:

SELECT TOP 5 i.* -- 用TOP代替LIMIT来限制条数
FROM items i
LEFT JOIN acquired_items ai ON i.item_id = ai.item_id 
  AND ai.user_id = 123
  AND ai.fetch_method = 'random'
WHERE ai.item_id IS NULL
ORDER BY NEWID();

Key notes #

• Performance heads-up: If your items table is large, ORDER BY RAND() (or equivalent) can be slow. For large datasets, you might need a more optimized approach (like generating a random row ID first), but the above works great for small to medium-sized tables.
• Tracking fetch methods: The fetch_method column in acquired_items ensures you only exclude items fetched via this random method—if you have other ways of fetching items, this keeps your data accurate.

内容的提问来源于stack exchange，提问作者Shaswat