SQL查询检查Mysql表中某些键是否存在

发布于 2024-10-15 19:32:29 字数 370 浏览 7 评论 0原文

我在我的应用程序中输入了一些静态文本键。我的应用程序还有一个可以使用的键表。我想编写一个查询来检查表中是否存在所有这些静态键。如果有些丢失了，我想知道它们是什么key。

这是一个示例表...

+----------+
|  column  |
+----------+
|   val1   |
+----------+
|   val2   |
+----------+
|   val3   |
+----------+

在此示例中，我想检查表中不存在哪些静态值 val1,val3,val4,val5。我该如何编写查询来实现此目的？

原文

I have some static text keys entered as part of my app. My app also has a table of keys that can be used. I want to write a query that checks whether all of those static keys exists in the table. If some are missing, I want to know what keys they are.

Here is an example table...

+----------+
|  column  |
+----------+
|   val1   |
+----------+
|   val2   |
+----------+
|   val3   |
+----------+

In this example, I want to check which of the static values val1,val3,val4,val5 don't exists in the table. How can I write a query to achieve this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

少女情怀诗 2024-10-22 19:32:29

select key_name from keys_tab 
    where key_name not in 
    (select t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_name = t1.key_name);

或者

select key_name from keys_tab 
    where key_name not in 
    (select t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_id = t1.key_id);

可以在子查询中使用 distinct

select key_name from keys_tab 
    where key_name not in 
    (select distinct t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_id = t1.key_id);

示例

mysql> select * from test1;
+----------+----------------------+---------------------+-------+
| col1     | col2                 | mdate               | myrow |
+----------+----------------------+---------------------+-------+
| col1     | 1/29/2011 9:59:47 AM | 2011-02-01 11:40:07 |  NULL |
| val2     | NULL                 | 2011-02-04 22:09:11 |  NULL |
| val4     | NULL                 | 2011-02-04 22:09:15 |  NULL |
+----------+----------------------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
| val2 | val3 |
| val4 | val3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test2 where col1 not in (select t2.col1 from test2 t2, test1 t1 where t2.col1 = t1.col1);
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
+------+------+
1 row in set (0.00 sec)

mysql>

您所需要的就是这个

CREATE TEMPORARY TABLE key_list(key_name varchar(100));
insert into key_list values ('val1'),('val2'),('val3'),('val4'),('val5');
select key_name from key_list where key_name not in (select distinct col1 from test1);

，然后如上所述使用该临时表。我确信一定有更好的方法。

创建表时可以使用 TEMPORARY 关键字。 TEMPORARY 表仅对当前连接可见，并在连接关闭时自动删除。

请参阅此处 http://dev.mysql.com/doc/refman /5.1/en/create-table.html

select key_name from keys_tab 
    where key_name not in 
    (select t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_name = t1.key_name);

select key_name from keys_tab 
    where key_name not in 
    (select t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_id = t1.key_id);

May use distinct in sub-query

select key_name from keys_tab 
    where key_name not in 
    (select distinct t2.key_name from keys_tab t2, other_tab t1 
       where t2.key_id = t1.key_id);

An Example

mysql> select * from test1;
+----------+----------------------+---------------------+-------+
| col1     | col2                 | mdate               | myrow |
+----------+----------------------+---------------------+-------+
| col1     | 1/29/2011 9:59:47 AM | 2011-02-01 11:40:07 |  NULL |
| val2     | NULL                 | 2011-02-04 22:09:11 |  NULL |
| val4     | NULL                 | 2011-02-04 22:09:15 |  NULL |
+----------+----------------------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
| val2 | val3 |
| val4 | val3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test2 where col1 not in (select t2.col1 from test2 t2, test1 t1 where t2.col1 = t1.col1);
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
+------+------+
1 row in set (0.00 sec)

mysql>

All you need is this

CREATE TEMPORARY TABLE key_list(key_name varchar(100));
insert into key_list values ('val1'),('val2'),('val3'),('val4'),('val5');
select key_name from key_list where key_name not in (select distinct col1 from test1);

and then use this temp table as mentioned above. I am sure there must be a better method.

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed.

see here http://dev.mysql.com/doc/refman/5.1/en/create-table.html

回复收藏 0 原文

~没有更多了~