如何避免重复的列值(how to avoid the duplicate column values)

如何避免重复的列值(how to avoid the duplicate column values)

需要从下表中获得那些没有时间参加2015年11月的ID

表格1：

id_no time-in S12 02-JUL-10 08.00.00 S12 07-OCT-10 10.00.00 S12 23-FEB-11 08.00.00 S12 01-FEB-14 09.00.00 S12 26-NOV-15 00.00.00 S68 15-MAR-06 08.00.00 S80 28-OCT-09 08.00.00 S80 07-OCT-10 10.00.00 S80 23-FEB-11 08.00.00

样本输出

id_no s68 s80

我不知道如何编写上述查询。 我尝试了下面

SELECT DISTINCT ID_NO FROM TABLE1 WHERE TO_CHAR(TIME_IN, 'YYYYMM') not in('201511');

我得到的输出为

id_no s12 s68 s80

我可以理解，它需要表中重复的ID条目。 我需要避免这种情况。 请帮助编写查询以将输出作为“样本输出”。

非常感谢您的任何建议。

Need to get those ids from the table below who dont have the time-in for november 2015

Table 1:

id_no time-in S12 02-JUL-10 08.00.00 S12 07-OCT-10 10.00.00 S12 23-FEB-11 08.00.00 S12 01-FEB-14 09.00.00 S12 26-NOV-15 00.00.00 S68 15-MAR-06 08.00.00 S80 28-OCT-09 08.00.00 S80 07-OCT-10 10.00.00 S80 23-FEB-11 08.00.00

sample output

id_no s68 s80

I dont know how to write the query for the above. I tried with the below

SELECT DISTINCT ID_NO FROM TABLE1 WHERE TO_CHAR(TIME_IN, 'YYYYMM') not in('201511');

I get the output as

id_no s12 s68 s80

I can understand that it take the repeated ids entries in the table. I need to avoid that. Please help to write the query for getting the output as " sample output".

many thanks for any suggestions.

最满意答案

SELECT DISTINCT ID_NO FROM TABLE1 WHERE TO_CHAR(TIME_IN, 'YYYYMM') <> ('201511') MINUS SELECT DISTINCT ID_NO FROM TABLE1 WHERE TO_CHAR(TIME_IN, 'YYYYMM') = '201511';

上述查询将在2015年11月份放弃s12。

SELECT DISTINCT ID_NO FROM TABLE1 WHERE TO_CHAR(TIME_IN, 'YYYYMM') <> ('201511') MINUS SELECT DISTINCT ID_NO FROM TABLE1 WHERE TO_CHAR(TIME_IN, 'YYYYMM') = '201511';

Above query will discard s12 as it has time in 2015 November.