使用单个查询从多个表中提取 [英] Use a single query to pull from mulitple tables

问题描述

我有几个查询，我觉得可以在 mySQL 中重构和完成，但我不知道如何去做.我正在以编程方式执行此操作，但我确信我可以加快速度.

I have a couple of queries that I feel could be refactored and done easier in mySQL but I'm not sure how to go about it. I'm doing it programtically but I'm sure I can speed this up.

基本上从用户那里获取一个 id，查看数据库并获取可能具有与给定参数相似的标签的行的 id.确保排除原始参数并且不包含任何重复的 ID.

Basically get an id from the user, look in the db and get the ids of rows that may have similiar tags as the given argument. making sure to exclude the original argument and not including any duplicate ids.

有没有办法在纯 sql 中做到这一点?

Is there a way to do this in pure sql?

这是我当前的代码:

def getRelatedEvents(self, memberId, eventId):
    relatated_events = []

    # first we get all the tags related to this event
    for tag in self.db.query("select tagName from event_tags where eventId={}".format(eventId)):
        # we iterate through each tag and find the eventIds for it
        events = self.db.query("SELECT eventId from event_tags where tagName LIKE %s and eventId != %s LIMIT 3",
                               '%'+tag['tagName']+'%', eventId)

    # we group them in a list, excluding ones that are already in here
        for id in events:
            if id['eventId'] not in relatated_events:
                relatated_events.append(id['eventId'])

    # we get the extra event info for each item in the related list and return
    return [self.getSpecificEvent(memberId, item) for item in relatated_events]

推荐答案

您应该能够通过自联接实现此目的，例如:

You should be able to achieve this with a self-join, like :

SELECT DISTINCT e2.eventId
FROM event_tags e1
INNER JOIN event_tags e2 
    ON e2.tagName LIKE CONCAT('%', e1.tagName, '%') AND e2.eventId != e1.eventId
WHERE e1.eventId = {}

我注意到第二个查询有一个 LIMIT 3 子句.首先，请注意，如果没有 ORDER BY 子句，这不会产生可预测的结果.这是一个基于窗口函数 ROW_NUMBER()(在 MySQL 8 中可用)的解决方案，它为每个匹配的标签生成不超过 3 个 event_id :

I notice that the second query has a LIMIT 3 clause. First of all, please note that without an ORDER BY clause this does not produces predictable results. Here is a solution based on window function ROW_NUMBER() (available in MySQL 8), that will produce not more than 3 event_id for each matching tag :

SELECT DISTINCT event_id FROM (
    SELECT e2.eventId, ROW_NUMBER() OVER(PARTITION BY e1.eventId ORDER BY e2.eventId) rn
    FROM event_tags e1
    INNER JOIN event_tags e2 
        ON e2.tagName LIKE CONCAT('%', e1.tagName, '%') AND e2.eventId != e1.eventId
    WHERE e1.eventId = {}
) WHERE rn <= 3

这篇关于使用单个查询从多个表中提取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！