SQL 常用语句

1、查找表中的重复记录：

select id from hb_vipuserinfo where id in (select id from hb_vipuserinfo group by id having count(id) > 1);

2、删除表中的重复记录，并保留rowid最小的记录(适合ORACLE)：

delete from hb_vipuserinfo where id in (select id from hb_vipuserinfo group by id having count(id ) > 1) and rowid not in (select min(rowid) from hb_vipuserinfo group by id having count(id ) > 1);

3、DB2中表数据导入导出的命令


导出

EXPORT TO myfile.del OF DEL MESSAGES msg.out
      SELECT staff.name, staff.dept, org.location
        FROM org, staff
       WHERE org.deptnumb = staff.dept;

含LOB字段
EXPORT TO 'F:\T_ITEM_CODE.del' OF DEL
  LOBS TO
    'F:\'
  MODIFIED BY
    LOBSINFILE
  MESSAGES 'F:\exportmsg.out'
  SELECT * FROM "DB2FRTER"."T_ITEM_CODE";

#SYNC 10;


导入

IMPORT FROM file_name OF file_type
    MESSAGES message_file
    [ INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE ]
    INTO target_table_name

4、删除重复数据

insert into tps_user_menu_old select * from tps_user_menu;

insert into tps_user_menu_new SELECT * FROM tps_user_menu
GROUP BY usercode, menuid HAVING COUNT(DISTINCT usercode)=1;