SQL语句去掉重复记录，获取重复记录

 SQL语句去掉重复记录，获取重复记录  

--查询一个表中有效去掉重复的记录，UserID为自增长主键，RoleID为重复字段 SELECT MIN(UserID) AS UserID, RoleID FROM tmpTable GROUP BY RoleID SELECT RoleID FROM tmpTable GROUP BY RoleID HAVING (COUNT(*) > 1) SELECT DISTINCT * FROM tmpTable sql语句查询 sql server access 数据库里的所有表名,字段名2007年02月01日 星期四 下午 04:21SQL SERVER  查看所有表名： select       name       from       sysobjects       where       type=''U'' 查询表的所有字段名： Select name from syscolumns Where ID=OBJECT_ID(''表名'') select * from information_schema.tables select * from information_schema.views select * from information_schema.columns  ACCESS 查看所有表名： select       name       from       MSysObjects       where       type=1       and       flags=0 MSysObjects是系统对象，默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来   DECLARE authors_cursor CURSOR forselect * from inhibit as awhere exists(select cidfrom inhibitwhere cid=a.cidgroup by cidhaving count(cid)>1)order by cid OPEN authors_cursordeclare @id0 numeric(18,0),@cid0 char(20),@other char(30) declare @id1 numeric(18,0),@cid1 char(20) FETCH NEXT FROM authors_cursor INTO @id0, @cid0, @other, @other, @other, @other, @other, @other --select @id0,@cid0  WHILE @@FETCH_STATUS = 0BEGIN         FETCH NEXT FROM authors_cursor       INTO @id1, @cid1, @other, @other, @other, @other, @other, @other        while(@cid1=@cid0 and @@FETCH_STATUS = 0)// cid相同的记录只留一个        begin          print @id1          delete from inhibit          where id=@id1          FETCH NEXT FROM authors_cursor           INTO @id1, @cid1, @other, @other, @other, @other, @other, @other        end      set @id0=@id1      set @cid0=@cid1END CLOSE authors_cursorDEALLOCATE authors_cursorgo