如何避免重复的列值(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.00sample output
id_no s68 s80I 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 s80I 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.
更多推荐
发布评论