SQL查询以基于键值列表有条件地检索记录 [英] SQL Query to conditionally retrieve records based on a key value list

问题描述

我有一个API，该API与具有以下表的数据库进行通信:

I have an API that communicates with a database with the following tables:

问题

+----------+------------+
| quest_id | quest_name |
+----------+------------+
|        1 | Q001       |
|        2 | Q002       |
|        3 | Q003       |
|        4 | Q004       |
|        5 | Q005       |
+----------+------------+

技能

+----------+------------+
| skill_id | skill_name |
+----------+------------+
|        1 | S001       |
|        2 | S002       |
|        3 | S003       |
|        4 | S004       |
|        5 | S005       |
+----------+------------+

SKILL_PREREQUISITES

+----------+-----------------------+-------------+
| quest_id | prerequisite_skill_id | skill_value |
+----------+-----------------------+-------------+
|        1 |                     2 |          50 |
|        1 |                     1 |          45 |
|        4 |                     2 |          25 |
|        4 |                     3 |          60 |
|        5 |                     4 |          50 |
+----------+-----------------------+-------------+

任务与游戏中的等级相对应，并且技能是玩家在玩游戏时获得的. SKILL_PREREQUISITE表保留了玩家能够参与任务之前需要满足的技能先决条件.

Quests correspond to levels in a game, and the skills are acquired by players while playing the game. The SKILL_PREREQUISITE table maintains the skill pre-requisites a player needs to satisfy before being able to participate in a quest.

问题

API的端点会接收玩家拥有的技能列表，以及相应技能的技能等级(skill_value)，如下所示:

An endpoint of the API receives a list of skills a player has, along with the skill level (skill_value) of the corresponding skill like so:

[
{
  "key": 1, //skill ID
  "value": 45 //skill value
},
{
  "key": 2,
  "value": 60
}
...
]

现在我的用例是，以使用这些值并查询数据库，以基于skill_id和skill_value

Now my use case is to use these values and query the database to obtain a list of Quests the player is eligible to participate in based on the skill_id as well as the skill_value

示例

假设API收到以下技能技能值映射:

Assume the API receives the following skill-skill value mapping:

skill-value map: [{1,50}, {2,60}, {4,50}]

基于此，具有上述技能的玩家可以参加任务1和5，但不能参加任务4，因为4要求技能3得60分.

Based on this, the player with the above skill set can participate in quest 1 and 5 but not in 4 since 4 requires skill 3 with 60 points.

尝试解决方案

我设法编写了一个查询(由于我之前发布了一个问题！)，以标识与技能ID对应的任务，但是我不知道如何根据技能值在查询中进一步过滤此查询.

I have managed to write a query (thanks to a previous question I posted!) to identify the quests that correspond to the skill IDs, but I have no idea how to filter this further within the query, based on the skill value.

我想知道这是否可能，并且是否可能需要在服务器上进行进一步处理才能获得所需的结果.

I am wondering if this is even possible at this point and whether I might have to do further processing on the server to get the required result.

我为解决方案做的准备: https://www.db-fiddle .com/f/s2umHS1wz3Q8ibwUhKDas6/1

Fiddle for my attempt at a solution: https://www.db-fiddle.com/f/s2umHS1wz3Q8ibwUhKDas6/1

推荐答案

您可以通过比较技能和值来用词组查询:

You can phrase the query by comparing both the skill and the value:

SELECT q.quest_id
FROM QUESTS q LEFT JOIN
     SKILL_PREREQUISITES p
     ON p.quest_id = q.quest_id
GROUP BY q.quest_id
HAVING SUM( p.prerequisite_skill_id = 1 and 50 >= p.skill_value) > 0 AND
       SUM( p.prerequisite_skill_id = 2 and 60 >= p.skill_value) > 0 AND
       SUM( p.prerequisite_skill_id = 4 and 50 >= p.skill_value) > 0 ;

因为您需要具有前提条件的任务，所以不需要LEFT JOIN.实际上，根本不需要JOIN

Because you need quests that have prerequisites, a LEFT JOIN is not necessary. In fact, no JOIN is necessary at all:

SELECT p.quest_id
FROM SKILL_PREREQUISITES p
WHERE p.prerequisite_skill_id IN (1, 2, 4)
GROUP BY p.quest_id
HAVING SUM( p.prerequisite_skill_id = 1 and 50 >= p.skill_value) > 0 AND
       SUM( p.prerequisite_skill_id = 2 and 60 >= p.skill_value) > 0 AND
       SUM( p.prerequisite_skill_id = 4 and 50 >= p.skill_value) > 0 ;

GROUP BY之前的过滤是可选的，但可以提高性能.

The filtering before the GROUP BY is optional, but it might improve performance.

我认为我回答了上面的错误问题.我认为您想要在满足用户必备条件的情况下可以使用的所有技能.那应该是:

I think I answered the wrong question above. I think you want all the skills that are available given the user's pre-requisites. That would be:

SELECT q.quest_id
FROM QUESTS q LEFT JOIN
     SKILL_PREREQUISITES p
     ON p.quest_id = q.quest_id
GROUP BY q.quest_id
HAVING COALESCE(SUM( (p.prerequisite_skill_id = 1 and 50 >= p.skill_value) +
                     (p.prerequisite_skill_id = 2 and 60 >= p.skill_value) +
                     (p.prerequisite_skill_id = 4 and 50 >= p.skill_value)
                   )) = COUNT(p.prerequisite_skill_id);

这篇关于SQL查询以基于键值列表有条件地检索记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！