这条SQl语句怎样用 Case Where end表达

[綏子]

if tb1.recondcount>500 THen
begin
Delete from tb1
Where Open_datetime in (select min(open_datetime) from tb1)
end else
//什么都不做。

求最优算法.

[aking630]

case tb1.recondcount>500 then Delete from tb1
Where Open_datetime in (select min(open_datetime) from tb1) else end

[綏子]

tb1.recondcount//这个函数在sql里没有，怎样把这句和上面的组合用
select count (*) as count from tb1 as A

[綏子]

up

[SayForever]

你的意思是想表的记录大于500条的时候删除最早进来的信息是吧，这可能不能用一条SQL语句搞定，你可在表中加一个触发器，每次增加记录时触发器自动判断。语句为：
Create Trigger TrgInsert On Table
For Insert
Declar @I Int
Select @I=Count(*) Trom Table1
IF @I>=500 then
Delete Table1 where Open_datetime in (select min(open_datetime) from tb1) else end
return

试试

[綏子]

是的。　谢谢你

如果用case when else end ，可以实现吗?

[SayForever]

我只用过Case When else end 查询过，删除数据到没试过，可以试试看。

[SayForever]

Delete Table1 Where Clo1 in
(Select Clo1 From Table1,(Select Count(*) 'Clo2' From Table1) b,
(Select Min(Clo2)'Clo2' from Table1)c
Where b.Clo2>4 and c.Clo2=table1.Clo2)

我这边调试通过看你那边可不可以

[hycao]

如果一定要用case when else end的话，试试这样写：
Delete a from tb1 a, (select min(open_datetime) as mindate, count(*) as arow from tb1) b
where a.Open_datetime in (case when b.arow>500 then b.mindate else '' end)