消息关闭
    暂无新消息!
原来的数据是:

这个查询出来的就是一个小区下面同门牌号的数据  写的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  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`


希望的结果数据是两条相同门牌号的保留一套房源  条件是(有租约的保留  没租约的删除  两者都没有租约  删除其中一个即可 )
数据照片如下:


请各位用sql完成这个  谢谢  也必须是sql  测试老大说的 必须用sql写

11个回答

︿ 0
改了下,用这个
DELETE house
FROM   house AS h
WHERE  room_detail <> ''
       AND show <> 1
       AND room_detail IS NOT NULL
       AND subdistrict_id <> 0
       AND id NOT IN (SELECT MAX(id)
                      FROM   house
                      WHERE  room_detail <> ''
                             AND show <> 1
                             AND room_detail IS NOT NULL
                             AND subdistrict_id <> 0
                      GROUP BY
                             subdistrict_id,
                             room_detail,
                             room_name)  
︿ 0
1. 把之前的查询结果保存到tmphouse

 create table tmphouse  
 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  
 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`
 
2. 由于没有租约表,我新建了一个  0 有租约,1 表示没有租约 。如果不是这样的,你要稍微修改下后面的
create table zuyue(house_id int , flag int)


3.租约表和前面的tmphouse join 起来保留在tmp2
create table tmp2
select  * from zuyue a inner join tmphouse b 
where a.house_id=b.id

4.group by 2次  
SELECT 
    room_detail , room_name , subdistrict_id,flag,max(house_id)
FROM
    tmp2
WHERE
    (room_detail , room_name, subdistrict_id, flag) IN (SELECT 
            room_detail, room_name, subdistrict_id, MAX(flag)
        FROM
            tmp2
        GROUP BY room_detail , room_name , subdistrict_id)
        group by room_detail , room_name , subdistrict_id ,flag;
︿ 0
-- #房源表(room_detail varchar(60),room_name varchar(40),subdistrict_id int,id int,show bit)
-- #租约表(house_id int,flag bit)
-- show=1,表示删除
-- flag=0,表示租约
-- 表关联,id=house_id
-- sql2000环境测试

-- 构造数据,默认 show=0
if object_id('tempdb..#房源表') is not null drop table #房源表
create table #房源表(room_detail varchar(60),room_name varchar(40),subdistrict_id int,id int,show bit)
insert into #房源表
select '6号67室','A',1,2176,0 union all
select '6号67室','A',1,2177,0 union all
select '12号303室','B101',6,14,0 union all
select '12号303室','B101',6,15,0 union all
select '1号1室','A',6,5,0 union all
select '1号1室','A',6,10,0 union all
select '三区3栋2号0504室','B',52353,464,0 union all
select '三区3栋2号0504室','B',52353,475,0

-- 为了测试,特意加了3个数据,有1个是 show=1,1个是不重复
insert into #房源表
select '八区8栋8号0808室','C',88888,888,1 union all
select '八区8栋8号0808室','C',88888,889,0 union all
select '八区9栋9号0909室','D',99999,999,0

-- 构造租约数据,默认 flag=0
if object_id('tempdb..#租约表') is not null drop table #租约表
create table #租约表(id int,house_id int,flag bit)
insert into #租约表
select 11,2176,0 union all --id=2176,2177,小的ID有租约
select 13,10,0 union all   --id=5,10,大的ID有租约
select 15,475,0 union all  --id=464,475,大的ID有租约
select 20,999,0            --这租约ID是不重复的

--测试数据创建完成,开始处理

--处理前的house
SELECT * FROM   #house
--租约表
SELECT * FROM   #sales

--得到出租的房源列表
;WITH salehouse AS (
         SELECT h.* 
         FROM   #house       AS h
                JOIN #sales  AS s
                     ON  h.id = s.house_id
         WHERE  s.flag = 0
     )
--将销售房源对应的多余房源的show改为1
UPDATE #house
SET    show = 1
FROM   #house AS h
       JOIN salehouse AS s
            ON  h.room_detail = s.room_detail
            AND h.room_name = s.room_name
            AND h.subdistrict_id = s.subdistrict_id
            AND h.id <> s.id
WHERE  h.show <> 1
       AND h.subdistrict_id <> ''
       AND h.room_detail IS NOT NULL
--处理完租约相关房源后的house表
SELECT * FROM   #house AS h
--接下来只需要将重复的数据保留一个即可(重复的数据都是没有租约的)
--找到重复的数据,并取到最大值(保留编号最大的那个)
;WITH repeathouse AS (
         SELECT *
         FROM   #house AS h1
         WHERE  h1.id IN (SELECT MAX(h.id)
                          FROM   #house AS h
                          WHERE  h.show <> 1
                                 AND h.subdistrict_id <> ''
                                 AND h.room_detail IS NOT NULL
                          GROUP BY
                                 h.room_detail,
                                 h.room_name,
                                 h.subdistrict_id
                          HAVING COUNT(*) > 1)
     )
--把重复的数据非最大的show改为1
UPDATE #house
SET    show = 1
FROM   #house AS h
       JOIN repeathouse AS s
            ON  h.room_detail = s.room_detail
            AND h.room_name = s.room_name
            AND h.subdistrict_id = s.subdistrict_id
            AND h.id <> s.id
WHERE  h.show <> 1
       AND h.subdistrict_id <> ''
       AND h.room_detail IS NOT NULL
--查看修改后的house
SELECT * FROM   #house AS h


这张图是原始的house数据

这张图是租约记录

这张图是处理了租约相关重复记录后的house表

这张图是处理了租约无关的重复记录的后的house表

偷了楼上的测试数据做了下,楼主看看能不能用
︿ 0
;with t as (select ROW_NUMBER() over (partition by room_name order by subdistrict_id desc) xh,* from house)
delete t where xh>1
非常简单的,你问的都是假大神吧?
︿ 0
总共有两张表有关系  house表里面有个小区ID  还有就是有个租约表  租约表里面有个house_id   当租约表里面有这个house_id并且flag=0就是有租约  相反  则没租约
︿ 0
[code=sql]

-- #房源表(room_detail varchar(60),room_name varchar(40),subdistrict_id int,id int,show bit)
-- #租约表(house_id int,flag bit)
-- show=1,表示删除
-- flag=0,表示租约
-- 表关联,id=house_id
-- sql2000环境测试

-- 构造数据,默认 show=0
if object_id('tempdb..#房源表') is not null drop table #房源表
create table #房源表(room_detail varchar(60),room_name varchar(40),subdistrict_id int,id int,show bit)
insert into #房源表
select '6号67室','A',1,2176,0 union all
select '6号67室','A',1,2177,0 union all
select '12号303室','B101',6,14,0 union all
select '12号303室','B101',6,15,0 union all
select '1号1室','A',6,5,0 union all
select '1号1室','A',6,10,0 union all
select '三区3栋2号0504室','B',52353,464,0 union all
select '三区3栋2号0504室','B',52353,475,0

-- 为了测试,特意加了3个数据,有1个是 show=1,1个是不重复
insert into #房源表
select '八区8栋8号0808室','C',88888,888,1 union all
select '八区8栋8号0808室','C',88888,889,0 union all
select '八区9栋9号0909室','D',99999,999,0

-- 构造租约数据,默认 flag=0
if object_id('tempdb..#租约表') is not null drop table #租约表
create table #租约表(id int,house_id int,flag bit)
insert into #租约表
select 11,2176,0 union all --id=2176,2177,小的ID有租约
select 13,10,0 union all   --id=5,10,大的ID有租约
select 15,475,0 union all  --id=464,475,大的ID有租约
select 20,999,0            --这租约ID是不重复的


-- 把有重复的房源找出来,放到临时表 #tmp
if object_id('tempdb..#tmp')is not null drop table #tmp
create table #tmp(room_detail varchar(60),room_name varchar(40),subdistrict_id int,id int,show bit)
insert into #tmp
 select a.room_detail,a.room_name,a.subdistrict_id,a.id,a.show from #房源表 a
 inner join (select room_detail,room_name from #房源表 where show=0 group by room_detail,room_name having count(1)>1) b
 on a.room_detail=b.room_detail and a.room_name=b.room_name

-- select * from #tmp

/*

-- 步骤1,查出有重复,但有租约的ID
select t.room_detail,t.room_name,t.subdistrict_id,t.id from #tmp t
inner join (select house_id from #租约表 where flag=0) z on z.house_id=t.id

-- 步骤2,查出有重复,但没租约的ID,取最大ID
select h.room_detail,h.room_name,max(h.subdistrict_id) as subdistrict_id,max(h.id) as id
from (select t.room_detail,t.room_name,t.subdistrict_id,t.id,t.show from #tmp t
      where t.id not in (select house_id from #租约表 where flag=0))h
group by h.room_detail,h.room_name having count(1)>1

*/

-- 上面的都是步骤1,步骤2;现在把步骤连接起来,更新#房源表的 show=1

-- 对比更新前
select h.*,z.house_id from #房源表 h
left join #租约表 z on h.id=z.house_id

update h1 set h1.show=1 
from #房源表 h1,(select t1.* from #tmp t1
                where t1.id not in (select t.id from #tmp t
                                     inner join (select house_id from #租约表 where flag=0) z on z.house_id=t.id
                                     union all
                                     select max(h.id) as id
                                     from (select t.room_detail,t.room_name,t.subdistrict_id,t.id,t.show from #tmp t
                                           where t.id not in (select house_id from #租约表 where flag=0))h
                                     group by h.room_detail,h.room_name having count(1)>1)
               )t2
                 
where h1.id=t2.id

-- 对比更新后
select h.*,z.house_id from #房源表 h
left join #租约表 z on h.id=z.house_id

/code]
︿ 0
另外你那句sql应该会报错吧,id并不在group中
如果都没有租约的话就这样写吧
DELETE house
FROM   house  AS h
WHERE id NOT IN 
        (
                SELECT max(id)
                FROM   house
                WHERE  room_detail <> ''
                       AND show <> 1
                       AND room_detail IS NOT NULL
                       AND subdistrict_id <> 0
                GROUP BY
                       subdistrict_id,
                       room_detail,
                       room_name
               
            )   
︿ 0
说非常简单那个朋友  请你拿出答案说服我!!!毕竟我问的都是几年的大神 不是骗你的  而且人家真的很棒 很帮助我  我不喜欢你那样说人家   而且你写的这个是什么  完全看不懂你写的啥  with前面什么都没有啊