选择不存在的东西 [英] Select back things that don't exist

问题描述

我有这张桌子.我想选择不存在的项目，以便创建它们.

I have this table. I want to select back the items that don't exist already, so I can create them.

table tags
+---------+-------+
| tagId   | name  |
|   1     | C     |
|   2     | DX    |
|   3     | CG    |

说SQL的样子:

select name from tags where name in ( 'C', 'CG', 'RX' )

返回'C'和'CG'，所以您知道必须创建'RX'.

You get back 'C' and 'CG', so you know you have to create 'RX'.

当'RX'还不存在时，是否有办法获取这样的MySQL语句以返回'RX'?

Is there a way to get a MySQL statement like this to return 'RX' instead, when 'RX' doesn't already exist?

推荐答案

让我们假设您的标签('c'，'cg'，'rx')位于名为tags_match的表中，其结构与上述相同

lets assume your tags ('c', 'cg','rx') are in a table called tags_match with the same structure as above

然后您可以执行以下操作:

then you could do this:

select tr.name 
from tags as tl 
    right join tags_match as tr 
    on tl.name = tr.name
where tl.name is null

这将找到tag_match中所有不在标签中的项目，因此这将为您提供所需的结果，但是不幸的是，您的标签('c'，'cg'，'rx')不在表中:(

This will find all the items in tags_match that are not in tags, so this would give you the desired result, but unfortunately your tags ('c', 'cg','rx') are not in a table :(

无论我们可以使用子查询来伪造"表

No matter we can use a subquery to 'fake' the table

select tr.name 
from tags as tl 
    right join (select 'cg' as name 
                union select 'c' as name 
                union select 'rx' as name) as tr 
    on tl.name = tr.name
where tl.name is null

尽管有点丑陋，但这还是可行的.如果您要测试的项目很多，则可能需要考虑创建一个真正的临时表.

Although its a little ugly, this will work. If you have lots of items you want to test you may want to consider creating a real temporary table.

这篇关于选择不存在的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！