编写sql语句实现如下功能，删除表中多余的重复记录

admin

-- 创建交易记录表
create table c_transInfo (
  id int(11) not null auto_increment,
  transDate datetime,
  cardNo varchar(3),
  type varchar(4),
  money decimal(10,2),
  remark varchar(10),
  primary key (id)
);
-- transInfo插入数据
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-06-20 14:30:30','002','转入',7234,'工资');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-06-20 16:46:49','003','转入',8326,'工资');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-06-20 16:46:49','003','转入',8326,'工资');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-02 15:09:55','001','支取',500,'');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-02 15:09:55','001','支取',500,'');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-02 17:18:22','005','支取',1000,'');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-03 09:40:04','006','转出',127,'支付宝');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-03 09:40:04','006','转出',127,'支付宝');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-03 09:40:04','006','转出',127,'支付宝');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-05 17:19:34','007','存入',1000,'');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-02 17:49:23','008','转出',800,'还钱');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-03 19:22:28','009','转出',512,'超市购物');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-05 22:42:08','002','支取',300,'');
insert into c_transInfo(transDate,cardNo,type,money,remark) values('2019-07-23 04:58:59','003','转出',421,'支付宝');

Sarbr

delete from emp  where rowid not in(select min(rowid) from test group by columnA,columnB)

song

delete from emp where rowid not in (select min(rowid) from emp group by name,age)

gaoye

select distinct 员工编号

from emp

liufangyuan

一张没有主键数据库表emp（员工表）删除重复数据！
delete from emp
 where emp.empno =

       (select empno from emp group by empno having count(1) > 1)

完整代码：

-- Create table
create table EMP
(
  EMPNO   VARCHAR2(20),
  EMPNAME VARCHAR2(20),
  DEPTNO  VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

--删除

delete from emp
 where emp.empno =

       (select empno from emp group by empno having count(1) > 1)

一个人的时光

delete from emp where rowid not in (select min(rowid) from emp group by name,age)

梦紫菱0124

select distinct * from emp

renmihe

--创建员工表
create table emp1(
id number;
name varchar2(8);
empsal number
);

--插入数据
insert into emp1 values(1,'张三',1200);
insert into emp1 values(2,'王五',4500);
insert into emp1 values(3,'李四',3600);
insert into emp1 values(1,'张三',1200);

--提交
commit;

--一张没有主键数据库表emp（员工表）删除重复数据
--使用rowid
--全部数据
select e.*,rowid from emp1 e order by id,name,empsal
--留下的数据
select id,name,empsal,min(rowid) from emp1 group by id,name,empsal
--删除数据
delete emp1 where rowid not in(
select min(rowid) from emp1 group by id,name,empsal)
--查询删除后的表数据
select * from emp1 

Cracia

delete from emp where rowid not in

(select min(rowid) from emp group by name,id)

罗鹏

DELETE from emp WHERE (id) IN ( SELECT id FROM emp GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM emp GROUP BY id HAVING COUNT(*) > 1);

WR

如果有id主键：

delete db_user  where id not in (select min(id) from db_user group by  age, sex,city)

没有id主键的情况使用rowid删除重读数据

delete emp where rowid not in (select max(rowid) from emp group by ename)

 

wangwei37079

D

hanqian

delete * from emp where id not in (select distinct id from emp )

740317431

select distinct * from user

王之神判

delete from emp where rowid not in (select min(rowid) from emp group by name,age)

wangyanqing

create table emp(

        id varchar(20),

        name varchar(20), 

        sex varchar(20),

        age number,

        phone varchar(20)

);

wangjiaxing

drop table emp;
create table emp(
id varchar2(32) ,
deviceName varchar2(10),
model varchar2(14),
producer varchar2(4),
buyDate varchar2(32),
life  number(4),
weight number(4),
newtime varchar2(32)
)

付玲

一张没有主键数据库表emp（员工表）删除重复数据！
delete from emp
 where emp.empno =
       (select empno from emp group by empno having count(1) > 1)

完整代码：
-- Create table
create table EMP
(
  EMPNO   VARCHAR2(20),
  EMPNAME VARCHAR2(20),
  DEPTNO  VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

雒鑫鑫

delete   YourTable   
where   [id]   not   in   ( 
select   max([id])   from   YourTable   
group   by   (name   +   value)) 

yanghao

DELETE EMP WHERE ID NOT IN (SELECT MAX(ROWID) FROM EMP GROUP BY ENAME);

m1359292934

select * from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1)