一 全部列操作
--
select * from sys_role_module t ;
insert into sys_role_module
select t . role_id , 14 from sys_role t ;
insert into sys_role_module
select t . role_id , 1401 from sys_role t ;
CREATE TABLE IF NOT EXISTS `temp` (
`role_id` bigint ( 11 ) NOT NULL COMMENT '角色id' ,
`module_id` int ( 11 ) NOT NULL COMMENT '模块id'
) ENGINE = InnoDB DEFAULT CHARSET = utf8 ;
delete from temp ;
insert into temp select distinct * from sys_role_module ;
delete from sys_role_module ;
insert into sys_role_module select * from temp ;
delete from temp ;
二 部分列操作
假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集 select identity(int,1,1) as autoID, * into #Tmp from tableName
oracle 可用suquence来代替identity 函数
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2) 最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)