一 全部列操作
--
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子句中省去此列)