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;
页:
[1]