像LIKE那样的Left Join需要LIMIT之类的东西

问题描述

(编辑:结尾的LIMIT无法解决.请仔细阅读.)

(EDIT: A trailing LIMIT will not solve. Please read carefully.)

我有一个表，可以按LEFT JOIN和LIKE在另一个表中查找文本，我想限制结果的数量 每左行 >.

I have a table that looks up text in another table by LEFT JOIN and LIKE and I would like to limit the amount of results per-left-row.

• 表usergames AS u包含用户输入的表示游戏名称的字符串的子字符串.
• 表games AS g包含游戏名称的主列表.

• 当用户输入游戏名称(如"Hotline Miami")时，输入将被拆分，并在substrs中创建2个条目:一个包含"hotline"&一个包含"miami"(在piece列中)，两个都包含完整的游戏名称(在name列中).

• Table usergames AS u contains substrings of user-inputted strings representing game names.
• Table games AS g contains a master list of game names.

• When the user enters a game name like "Hotline Miami", the input is split and 2 entries are created in substrs: one containing "hotline" & one containing "miami" (in the piece column), and both containing the full game name (in the name column).

如果完整游戏名称与主列表中的所有内容都不完全匹配(例如，用户错误输入"Haatline Miami")，我会检查主列表中的建议，例如带有"Haatline"或"Miami".

In the event that the full game name does not match anything exactly in the master list, (ex. user mistakenly enters "Haatline Miami"), I check the master list for suggestions, such as any game with "Haatline" or "Miami".

这是我的查询，效果很好:

This is my query and it is working great:

SELECT u.name,g.name AS suggestion,count(g.name)
FROM usergames u
LEFT JOIN games g
ON CONCAT(' ', g.name, ' ') LIKE CONCAT('% ', u.piece, ' %')
GROUP BY g.name, u.name
ORDER BY u.name ASC, count(g.name) DESC

我的问题是，有时有太多建议.也许有1000个游戏带有"Miami"一词.如何限制联接每左行"创建的许多行?请参阅下面的数据，一些结果来自热线"，一些结果来自迈阿密".如何限制每个子字符串X个结果.

The problem I have is that sometimes there are too many suggestions. Maybe there are 1000 games that have the word "Miami". How can I limit the many rows created by the join "per left row"? See the data below, some results are from "hotline" and some from "miami". How could I limit to X results per substring.

样品数据

substrs

games

result

推荐答案

所以，这是需要做的:2个子查询和2个带有联接表的交叉联接.我仍然不知道如何限制子查询结果的数量，但是这种解决方案使它的工作速度如此之快，以至于减少需求大大减少了. (limit仍然不适用.)

So, here is what it took: 2 subqueries and 2 cross joins with a tally table. I STILL don't know how to limit the number of subquery results but this solution makes it work so much faster now that the need to reduce is greatly diminished. (limit is still not applicable.)

首先，由于我的兄弟，我了解了带有计数表的CROSS JOIN.这样，我就能提出这个疯狂的解决方案.如果有人在查看我的解决方案后知道更好的方法，请发布:

First I learned about CROSS JOINs with tally tables, thanks to my brother. With that I was able to come up with this crazy solution. If anyone knows of a better way after reviewing my solution, please do post:

SELECT us.name, gs.idGame, suggestion, count(suggestion)
FROM (
    SELECT u.idGame, u.name, SUBSTRING_INDEX(SUBSTRING_INDEX(u.name, ' ', n.n), ' ', -1) AS user_substr
    FROM usergames u
    CROSS JOIN (
        SELECT N FROM _tally
    ) n
    WHERE u.idGame IS NULL
    AND n.n <= 1 + (LENGTH(u.name) - LENGTH(REPLACE(u.name, ' ', '')))
    HAVING LENGTH(user_substr) > 1
) us, 
(
    SELECT idGame, g.name suggestion, SUBSTRING_INDEX(SUBSTRING_INDEX(g.name, ' ', n.n), ' ', -1) AS game_substr
    FROM games g
    CROSS JOIN (
        SELECT N FROM _tally
    ) n
    WHERE n.n <= 1 + (LENGTH(g.name) - LENGTH(REPLACE(g.name, ' ', '')))
    HAVING LENGTH(game_substr) > 1
) gs
WHERE user_substr NOT IN (
    SELECT piece from _piecesrestricted   
)
AND game_substr NOT IN (
    SELECT piece from _piecesrestricted   
)
AND (
    LENGTH(user_substr)>3 OR
    user_substr = suggestion
)
AND user_substr = game_substr
GROUP BY suggestion, us.name
ORDER BY us.name ASC, count(suggestion) DESC

这篇关于像LIKE那样的Left Join需要LIMIT之类的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！