用 INSERT INTO test values(428101,'123456',1,0,0); INSERT INTO test values(428102,'123456',1,0,0); INSERT INTO test values(428104,'123456',1,0,1); INSERT INTO test values(428114,'1234567',1,0,0); INSERT INTO test values(428115,'1234567',1,0,1); INSERT INTO test values(428131,'1234567',1,0,0); INSERT INTO test values(428149,'12345678',2,1,1); INSERT INTO test values(428150,'12345678',2,1,0); INSERT INTO test values(428174,'123456789',3,0,0); INSERT INTO test values(428175,'1234567890',3,0,1); INSERT INTO test values(428176,'111111',3,2,0); INSERT INTO test values(428187,'111111',3,1,0); INSERT INTO test values(428191,'111111',3,0,1);数据,正确结果是什么
select * from test a WHERE NOt EXISTS (select 1 from test where number=a.number and flag_1=a.flag_1 and a.flag_2=flag_2 and a.id<id) and a.delete_true_or_false=0
select * from test t where delete_true_or_false=0 and not exists (select 1 from test where number=t.number and flag_1=t.flag_1 and flag_2=t.flag_2 and id>t.id)
如果删除数据太多,建议重新建立新表 create table newtt as select * from test a WHERE NOt EXISTS (select 1 from test where number=a.number and flag_1=a.flag_1 and a.flag_2=flag_2 and a.id<id) and a.delete_true_or_false=0
delete a from test a,( select id from test t where delete_true_or_false=0 and not exists (select 1 from test where number=t.number and flag_1=t.flag_1 and flag_2=t.flag_2 and id>t.id) )b where a.id !=b.id
如果考虑效率,则不建议用一条SQL语句来实现,可以通过存储过程或者程序来实现。
错了。。 狼哥这条SQL,我所有数据都删了
假设ID唯一 delete b from test b left join (SELECT * FROM TEST A WHERE not EXISTS (select 1 from test where number=a.number and flag_1=a.flag_1 and a.flag_2=flag_2 and a.id<id) and a.delete_true_or_false=0) a1 on b.id=a1.id where a1.id is null;
INSERT INTO test values(428101,'123456',1,0,0);
INSERT INTO test values(428102,'123456',1,0,0);
INSERT INTO test values(428104,'123456',1,0,1);
INSERT INTO test values(428114,'1234567',1,0,0);
INSERT INTO test values(428115,'1234567',1,0,1);
INSERT INTO test values(428131,'1234567',1,0,0);
INSERT INTO test values(428149,'12345678',2,1,1);
INSERT INTO test values(428150,'12345678',2,1,0);
INSERT INTO test values(428174,'123456789',3,0,0);
INSERT INTO test values(428175,'1234567890',3,0,1);
INSERT INTO test values(428176,'111111',3,2,0);
INSERT INTO test values(428187,'111111',3,1,0);
INSERT INTO test values(428191,'111111',3,0,1);数据,正确结果是什么
428131 1234567 1 0 0
428150 12345678 2 1 0
428174 123456789 3 0 0
428176 111111 3 2 0
428187 111111 3 1 0
就是这个啊。。上面有哦。。跪求大师指点.
delete_true_or_false这一列为1的删除.
delete_true_or_false这一列为0的删除.
PS:delete_true_or_false只可能是0或1
那不就是直接删除,和 delete_true_or_false这一列 的值没有任何关系?
(select 1 from test where number=a.number and flag_1=a.flag_1 and a.flag_2=flag_2 and a.id<id)
and a.delete_true_or_false=0
from test t
where delete_true_or_false=0
and not exists (select 1 from test where number=t.number and flag_1=t.flag_1 and flag_2=t.flag_2 and id>t.id)
错了。错了。。呜呜
2.当number,flag_1,flag_2三列相同的只有一行时,
delete_true_or_false这一列为1的删除.
delete_true_or_false这一列为0的不删除.
在number,ID上建立索引
我如果想直接删除那些不要的数据,我要怎么写?
直接用delete删除那些不要的数据,不用select查询.
create table newtt as
select * from test a WHERE NOt EXISTS
(select 1 from test where number=a.number and flag_1=a.flag_1 and a.flag_2=flag_2 and a.id<id)
and a.delete_true_or_false=0
select id
from test t
where delete_true_or_false=0
and not exists (select 1 from test where number=t.number and flag_1=t.flag_1 and flag_2=t.flag_2 and id>t.id)
)b where a.id !=b.id
错了。。 狼哥这条SQL,我所有数据都删了
delete b from test b left join (SELECT * FROM TEST A WHERE not EXISTS
(select 1 from test where number=a.number and flag_1=a.flag_1 and a.flag_2=flag_2 and a.id<id)
and a.delete_true_or_false=0) a1
on b.id=a1.id where a1.id is null;