连接相等的子串？
我有一个游戏和价格数据库，最近我在其中发现了另一个问题。有些游戏会在数据库中出现两次 - 一次为 PC 版本，一次为 Mac 版本。如果游戏有两个这样的版本，我的数据库中不需要 mac 版本。不过，如果游戏只有 mac 版本，我想保留它。
这个问题对我来说变得更简单了，因为所有也有 mac 版本的游戏都将使用完全相同的名称，并在末尾加上一个小片段来表明它适用于 mac。其形式可能为“Left 4 Dead 2 (Mac)”、“Left 4 Dead 2 Mac”或“Left 4 Dead 2 [Mac 下载]”。
为了尝试找到这些条目，我创建了以下查询：
SELECT *
FROM `gamelist` g1
JOIN `gamelist` g2
ON
SUBSTR(g1.`title`,0,20) = SUBSTR(g2.`title`,0,20)
AND
g1.`title`<>g2.`title`
WHERE
(g1.`amazon_id` IS NOT NULL AND g2.`amazon_id` IS NOT NULL)
OR (g1.`steam_id` IS NOT NULL AND g2.`steam_id` IS NOT NULL)
OR (g1.`impulse_id` IS NOT NULL AND g2.`impulse_id` IS NOT NULL)
...
这个想法应该相当简单。问题是 SUBSTR() 似乎根本不起作用。在大约有 8,000 个条目的数据库中，它返回了大约 6400 万条结果。显然，它完全跳过 SUBSTR(g1.title,0,20) = SUBSTR(g2.title,0,20) 行并随时加入行 g1.title<>g2 .title
我怎样才能像这样加入相等的子字符串？
I have a database of games and prices, in which I recently found yet another issue. There are some games which appear in the database twice - once for the PC version and once for the Mac version. If there are two such versions of a game, I don't need the mac version in my database. If there is ONLY a mac version of the game, however, I would like to keep it.
The issue was made a bit simpler for me since all games that also have a mac version will use the exact same name plus a small snippet at the end to indicate it's for mac. This might take the form "Left 4 Dead 2 (Mac)", "Left 4 Dead 2 Mac", or "Left 4 Dead 2 [Mac Download]".
To try and find these entries I created the following query:
SELECT *
FROM `gamelist` g1
JOIN `gamelist` g2
ON
SUBSTR(g1.`title`,0,20) = SUBSTR(g2.`title`,0,20)
AND
g1.`title`<>g2.`title`
WHERE
(g1.`amazon_id` IS NOT NULL AND g2.`amazon_id` IS NOT NULL)
OR (g1.`steam_id` IS NOT NULL AND g2.`steam_id` IS NOT NULL)
OR (g1.`impulse_id` IS NOT NULL AND g2.`impulse_id` IS NOT NULL)
...
The idea should be fairly straightforward. The issue is that the SUBSTR() didn't seem to work at all. In a database with roughly 8,000 entries it returned about 64 million results. Obviously it's completely skipping the SUBSTR(g1.title,0,20) = SUBSTR(g2.title,0,20) line and joining the rows any time g1.title<>g2.title
How can I join on equal substrings like this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SUBSTR()，按照您使用的方式，是一索引的，而不是零索引的。你想要
SUBSTR(), in the manner you're using it, is one-indexed, not zero-indexed. You want