sql的问题 （问了很多大神都无果）

[qq_39549545]

前几天接到一个需求 删除原来由于(移动产生的错误数据) 现在想修复!想着代码只执行一次 朋友推荐写一个定时程序只执行一次 到时候执行之后就删除代码 我便开始写了 定时程序写完了 并且测试也测试了都没有问题 但是测试老大就说你这个需求就不应该用定时程序来实现!!首先用定时程序来实现就是错误的!!这个sql足以完成!!于是我问他sql咋写？他自己弄了半天 说我也写不来 你问问的其他的人!!!但是我肯定sql绝对可以!!于是我这两天都在想用sql怎么实现!!!不过本人愚笨!!想了好久都无果!!!于是需求了很多大神!!!!java大神!php 大神都有!!都说这个太难!!sql根本没法完成!!于是到这里来找好心的大神帮帮我!!!!非常感谢!!!!!

首先需求原版是这样的:
现在存在一个小区下面有很多同门牌号的房子!但是相同门牌号的房子不可能等于三套或者超过三套!也就是说最多两套 两套的房子里面(就是门牌号完全相同的房子) 有两种可能!1;一个房子有租约一个房子没有租约!租约就是被人租了!反之则相反!! (有租约的我们就保存!没有租约的我们就要把状态改成删除的状态 )2 ：两个房子都没有租约(保留任意一个房子 另一个房子的状态必须改成删除) 小区下面的房子总共有两种情况 一种是合租 一种是整租 上面一直说的就是合租的情况下 还有就是整租的情况 整租也是同理 有两套房子一模一样的 整租的 我们要删除其中一个保存其中一个


现在相关联的表有以下三张表(house(房源表) subdistrict(小区表) contract(租约表))
表之间的关系是 house表里面的subdistrict_id=subdistrict(小区表里面的id) house 表里面的id=租约表里面的house_id
其次 最重要的表就是house表 house里面的字段 room_detail+room_name就是门牌号 room_name为空的就是整租房源
只有room_detail的就是异常房源即不做处理!! 现在我已经有把同门牌号下面的所有相同的房源都查出来了 sql如下:
SELECT h.room_detail,h.room_name,h.subdistrict_id,h.id FROM `house` as h JOIN (SELECT room_detail,room_name,subdistrict_id,id FROM `house` WHERE `subdistrict_id` in(SELECT `subdistrict_id` FROM house) AND room_detail <> '' AND `show` <> 1 AND `room_detail` is not null AND `subdistrict_id` <> 0 GROUP BY `subdistrict_id`,`room_detail`,`room_name` HAVING COUNT(1) >1) as temp ON h.`room_detail`=temp.`room_detail` AND h.`room_name`=temp.`room_name` AND h.`subdistrict_id`=temp.`subdistrict_id` 希望能够得到最佳答案 谢谢

[shoppo0505]

看的有点晕。 给出测试数据，期望结果，以及算法说明，再问吧。

[zhouyuehai1978]

如果你的错误数据会持续产生，那么就不可能用一句sql解决，因为删除了还会再出来。 如果只是由于历史原因造成，以后不会再出现该类问题，那么肯定可以一次搞定的。

[qq_39549545]

对 你非常聪明 我现在就是由于某一个功能引发的病因 现在数据里面有这些数据 现在线上不会再有这样的情况 已解决 现在这个只需要执行一次 你能加我QQ嘛？我们私聊 希望你指教

[baidu_35289351]

你的是什么数据库？ mysql? ms sql2000?2005?2008?... 你的问题，好像不难，

[道素]

你要求的是，对于相同的房间数据，优先保留有合同的数据吗？ 下面返回结果总DeleteFlag=1 就是作了删除标记的

drop table if exists `house`;
create table `house`(room_detail varchar(100),room_name varchar(100),subdistrict_id int,id int,`show` int,delflag int);
drop table if exists `subdistrict`;
create table `subdistrict`(id int, subdistrict_name varchar(10));
drop table if exists `contract`;
create table `contract`(id int, house_id int);

insert into `subdistrict`(id,subdistrict_name)values(1,'S1'),(2,'S2');
insert into `house`(room_detail,room_name,subdistrict_id,id,`show`)values('01','1CON',1,1,0),('01','1CON',1,2,0),('01','NOCON',1,3,0),('01','NOCON',1,4,0)
                   ,('02',null,2,5,0),('02',null,2,6,0),('05','BB',2,7,0),('07','2con',2,8,0),('07','2con',2,9,0);
insert into `contract`(id,house_id)values(1,1),(2,9),(3,5),(4,6);

select h.*,seq,HasContract,case when p.seq>1 then 1 else 0 end as DeleteFlag from 

 `house` h inner join (
    SELECT t.id
    , HasContract
    ,case when t.room_detail=@lastrd and @lastrn=ifnull(t.room_name,'') then @i:=@i+1 else @i:=1 end as Seq
    ,@lastrd:=t.room_detail as v_room_detail,@lastrn:=ifnull(t.room_name,'') as v_room_name
    from (
       SELECT h.room_detail,h.room_name,h.subdistrict_id,h.id ,case when c.id is null then 0 else 1 end as HasContract
       FROM `house` as h
       left join `contract` as c on c.house_id=h.id
       where `show` <> 1
    ) as t
    ,(select @i:=1,@lastrd:='',@lastrn:='') x
    order by t.room_detail,t.room_name,t.HasContract desc
) p on p.id=h.id;
##where p.seq>1;

  	room_detail	room_name	subdistrict_id	id	show	delflag	seq	HasContract	DeleteFlag
1	01	1CON	1	1	0	NULL	1	1	0
2	01	1CON	1	2	0	NULL	2	0	1
3	01	NOCON	1	3	0	NULL	1	0	0
4	01	NOCON	1	4	0	NULL	2	0	1
5	02	NULL	2	5	0	NULL	1	1	0
6	02	NULL	2	6	0	NULL	2	1	1
7	05	BB	2	7	0	NULL	1	0	0
8	07	2con	2	8	0	NULL	2	0	1
9	07	2con	2	9	0	NULL	1	1	0