SQL:如何只选择一个不显示ID的记录 [英] Sql: How can I select only one record which non present an id

问题描述

我使用Informix数据库，其中有一个表 coord :

+--------------------+-------------+
| Field              | Type        |
+--------------------+-------------+
| cm_key_coord_code  | char(8)     |
| cm_t_coor          | int         |
| descr_coord        | char(30)    |
+--------------------+-------------+

其中包含如下所示的数据:

+-cm_key_coord_code-+-cm_t_coor-+-descr_coord-+
+ 00000001          +    1      + coord01a    +
+ 00000001          +    2      + coord01b    +
+ 00000002          +    1      + coord02a    +
+ 00000002          +    2      + coord02b    +
+ 00000003          +    1      + coord03a    +
+ 00000004          +    2      + coord04a    +
+ 00000005          +    1      + coord05a    +
+-------------------+-----------+-------------+

该表具有多个记录，这些记录具有相同的cm_key_coord_code，但具有不同的cm_t_coor.如何选择仅具有cm_t_coor！= 2的行的cm_key_coord_code?

我只选择以下值:

+-cm_key_coord_code-+-cm_t_coor-+-descr_coord-+
+ 00000003          +    1      + coord03a    +
+ 00000005          +    1      + coord05a    +
+-------------------+-----------+-------------+

解决方案

您也可以使用NOT EXISTS子句来做到这一点.试试:

SELECT c.cm_key_coord_code
FROM coord c
WHERE NOT EXISTS
(SELECT 1
 FROM coord c2
 WHERE c2.cm_key_coord_code = c.cm_key_coord_code
 AND c2.cm_t_coor = 2)

I use the Informix database where I have a table coord :

+--------------------+-------------+
| Field              | Type        |
+--------------------+-------------+
| cm_key_coord_code  | char(8)     |
| cm_t_coor          | int         |
| descr_coord        | char(30)    |
+--------------------+-------------+

which contains data such as those shown below:

+-cm_key_coord_code-+-cm_t_coor-+-descr_coord-+
+ 00000001          +    1      + coord01a    +
+ 00000001          +    2      + coord01b    +
+ 00000002          +    1      + coord02a    +
+ 00000002          +    2      + coord02b    +
+ 00000003          +    1      + coord03a    +
+ 00000004          +    2      + coord04a    +
+ 00000005          +    1      + coord05a    +
+-------------------+-----------+-------------+

The table has multiple record with same cm_key_coord_code, but different cm_t_coor. How can I select cm_key_coord_code that have only rows with cm_t_coor != 2 ?

I want to select only the following values:

+-cm_key_coord_code-+-cm_t_coor-+-descr_coord-+
+ 00000003          +    1      + coord03a    +
+ 00000005          +    1      + coord05a    +
+-------------------+-----------+-------------+

解决方案

You could do this with a NOT EXISTS clause as well. Try:

SELECT c.cm_key_coord_code
FROM coord c
WHERE NOT EXISTS
(SELECT 1
 FROM coord c2
 WHERE c2.cm_key_coord_code = c.cm_key_coord_code
 AND c2.cm_t_coor = 2)

这篇关于SQL:如何只选择一个不显示ID的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！