减去mysql查询结果
减去mysql查询结果
提问于 2013-01-11 03:17:31
我正在尝试从两列中减去查询结果。
表：
id | word1 | lang1 | word2 | lang2 |
----+-----------+-------+-----------+-------+
1 | car | 1 | car | 15 |
2 | table | 1 | table | 15 |
3 | Chair | 1 | cahair | 13 |
4 | CDplayer | 15 | CDplayer | 1 |
5 | car | 1 | car | 13 |我想要获取语言1中所有尚未翻译成语言12的word1单词。因此，在这种情况下，它将是主席
表中有300万行，运行以下查询需要1分钟：
SELECT DISTINCT word1
FROM `translations`
WHERE lang1 = 1
AND lang2 != 15
AND NOT IN (SELECT word1 FROM `translations` WHERE lang2 == 15)
LIMIT 10分别对这两行执行select操作非常快0.006s，然后我可以在PHP中使用array_diff()将它们相减，但在MySQL中可能有一种更简单的方法来直接做这件事。
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-01-11 04:23:35
SELECT
origin.word1
FROM
( SELECT DISTINCT word1
FROM tableX
WHERE lang1 = 1
) AS origin
WHERE
NOT EXISTS
( SELECT *
FROM tableX AS trans
WHERE trans.lang1 = 1
AND trans.lang2 = 15
AND trans.word1 = origin.word1
) ;在运行这些查询之前，我会在(lang1, word1)和(lang1, lang2, word1) 上添加一个索引。
您也可以尝试此变体(并检查两个解释计划)：
SELECT DISTINCT
word1
FROM
tableX AS origin
WHERE
lang1 = 1
AND
NOT EXISTS
( SELECT *
FROM tableX AS trans
WHERE trans.lang1 = 1
AND trans.lang2 = 15
AND trans.word1 = origin.word1
) ;Stack Overflow用户
发布于 2013-01-11 03:58:42
这应该很简单
SELECT
m.id ,
l.*
FROM mytable m
INNER JOIN (
SELECT
*
FROM mytable
WHERE lang2 != 15
GROUP BY id
) as l ON l.id = m.id
WHERE l.lang1 = 1
GROUP BY m.idStack Overflow用户
发布于 2013-01-11 03:59:28
SELECT DISTINCT NonTranslated.word1
from
(SELECT DISTINCT word1 FROM `translations` WHERE lang1 = 1 AND lang2 != 15)NonTranslated
left join
(SELECT DISTINCT word1 FROM `translations` WHERE lang1 = 1 AND lang2 = 15)Translated
on NonTranslated.word1 = Translated.word1
where Translated.word1 is NULL;让我知道这个的解释是什么。我认为它可能比select子查询更快。
PS :假设:即使单词已经被翻译过一次，它也不会被包括在列表中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14265596
复制相关文章
相似问题
腾讯云开发者
