一些有用的 SQL 语句

1、去除重复的值：

如果想要查找 表（table_name）中的重复数据（以 t.vol_name1 为依据）的sql语句如下：
select *
  from (select t.*,
               row_number() over(partition by t.vol_name1 order by vol_name2) rn
          from table_name t) t
 where t.rn > 1

该语句可以扩展为删除重复的数据，只需要在该sql 语句的外面嵌套一层 delete 语句就可以了。

2： 用于任务分配：
select t5.*
  from (select t1.st_id,
               t1.sp_id,
               t1.ss_name sn1,
               t1.status,
               t2.ss_name sn2,
               t2.ss_thread
          from test_task t1, test_server t2
         where t2.ss_thread < 5) t5
 where t5.status = 0
   and not EXISTS (select t3.st_id, t3.sp_id, t3.sn2
          from (select t1.st_id,
                       t1.sp_id,
                       t1.ss_name sn1,
                       t1.status,
                       t2.ss_name sn2,
                       t2.ss_thread
                  from test_task t1, test_server t2
                 where t2.ss_thread < 5) t3
         where LOWER(t3.sn2) = LOWER(t3.sn1)
           and t3.sn2 = t5.sn2)