代码之家 › 专栏 › 技术社区 › Bryan

删除循环

count loops sql

Bryan · 技术社区 · 16 年前

我有一张住房清单。我希望每个城市最多保留10个列表。 (大多数城市的房源不到10个)。

当我执行此查询时:

 select city, count(city) as cityCount from tREaltyTrac group by city

SQL返回:

Acampo  1
Acton   1
Adelanto    20
Agua Dulce  1
Aguanga 1
Akron   19
Albany  12
Albion  3
Alexandria  14
Algonac 1
Alhambra    5

因此,阿德兰托、阿克伦、奥尔巴尼和亚历山大必须减少到只有10个列表,由最近的日期时间字段“creation”(按creation desc排序)排序。

有人能想到一个可以像描述的那样减少列表的过程吗?

6 回复 | 直到 10 年前

KM. 16 年前

不要循环!

我喜欢用状态标记这些行,但这将按您的需要执行并删除它们…

尝试此操作(SQL Server)

设置表

create table tREaltyTrac (city varchar(20),creation datetime)
insert into tREaltyTrac values ('Acampo'      ,getdate()) --1
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Acton'       ,getdate()) --1
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Adelanto'    ,getdate()) --20
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Agua Dulce'  ,getdate()) --1
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Aguanga'     ,getdate()) --1
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Akron'       ,getdate()) --19
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albany'      ,getdate()) --12
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albion'      ,getdate()) --3
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albion'      ,getdate()) --3
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Albion'      ,getdate()) --3
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alexandria'  ,getdate()) --14
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Algonac'     ,getdate()) --1
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alhambra'    ,getdate()) --5
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alhambra'    ,getdate()) --5
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alhambra'    ,getdate()) --5
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alhambra'    ,getdate()) --5
waitfor delay '00:00:01'
insert into tREaltyTrac values ('Alhambra'    ,getdate()) --5

显示表值

select city,count(*) from tREaltyTrac group by city
select * from tREaltyTrac

删除不需要的行

delete from tREaltyTrac
    from tREaltyTrac
        inner join (select
                        city,creation, row_number() over(partition by city order by city) AS RankValue
                        from tREaltyTrac
                   ) dt on tREaltyTrac.city=dt.city AND tREaltyTrac.creation=dt.creation
    where dt. RankValue>10

显示您剩下的行

select * from tREaltyTrac
select city,count(*) from tREaltyTrac group by city

jblaske 16 年前

像这样的事情应该帮你解决,但是自动删除记录不是一个好主意。你最好用一个活动的旗子。

DECLARE @CityName VARCHAR(30)
DECLARE CitiesOver10 CURSOR FOR select city from tREaltyTrac group by city having count(city)>10 

OPEN CitiesOver10
FETCH NEXT FROM CitiesOver10 INTO @CityName

WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE FROM 
            dbo.tREaltyTrac 
        WHERE 
            ID NOT IN (SELECT TOP 10 ID FROM dbo.tREaltyTrac WHERE city = @CityName ORDER BY Creation DESC)
            AND City = @CityName

        FETCH NEXT FROM CitiesOver10 INTO @CityName
    END

CLOSE CitiesOver10
DEALLOCATE CitiesOver10

DForck42 16 年前

您不应该从数据库中删除记录。这不是百分之百的完美,我相信有更好的方法可以做到,但现在你走吧。

declare @cities (CityName nvarchar(50), ID int identity(1,1))
declare @returns (CityName nvarchar(50), Blah nvarchar(50))
declare @cityname nvarchar(50)
declare @count int
declare @i int


insert into @cities (CityName)
select distinct CityName
from tblCities

select @count = count(*) from @cities
set @i=1

while (@i<=@count)
begin

    select @cityname = CityName from @cities where ID=@i

    select top 10 *
    from tblCities
    where CityName=@cityname
    order by Creation desc

    set @i=@i+1
end

Matt 16 年前

我现在没有时间写出实际的代码,但是这样的代码怎么样……(假设PHP)

订购初始查询-添加 创建DESC
循环遍历结果,并获取第10个结果的创建日期
运行另一个查询,删除所有小于第10个结果的创建值的内容?-即 删除创建位置<$creationdateoftresult

希望这有意义…

Ichorus 16 年前

我会选择前十个,将它们放入临时表中,删除原始数据并用临时表填充它。

Jay 16 年前

取决于您的SQL版本。类似的事情可能会奏效

选择R.City,B.。* 来自叛国者加入B.ID上的Trealytrac B (从trealtytrac中选择前10个ID,其中id=r.id order by id desc) R市集团