日期:2014-05-18  浏览次数:20680 次

删除一个表中同个资料最大的那个值,快来帮帮我!
例如表aa:
aa         bb           cc
1             255         20061212
1             15           20061210
2             33           20060612
2               11           20060101
按cc用DESC排序了,
如我想删除where   aa= '2 'or   其他中处的最近时间资料.谢谢!


------解决方案--------------------
delete a from tablename a where cc=(select max(cc) from tablename where aa=a.aa)

------解决方案--------------------
delete aa from tablename where cc=(select max(cc) from tablename group by aa)
------解决方案--------------------
-- 你的题目没讲清楚,我在此也懒得去臆测,虽然下边这段代
-- 码也许不能给你答案,但可以给你一个思路.

declare @aa table (
aa int ,
bb int ,
cc char(8)
)

insert into @aa (aa , bb , cc)
select 1 , 255 , '20061212 ' union all
select 1 , 15 , '20061210 ' union all
select 2 , 33 , '20060612 ' union all
select 2 , 11 , '20060101 '

--/进行删除操作以前,必须进行 select 操作,先看清楚要删的是否正确.
select *
from @aa a
where cc = (
select max(cc)
from @aa
where aa = a.aa
)

-- 经上一步确认无误后再执行下边一步,进行删除.
delete a
from @aa a
where cc = (
select max(cc)
from @aa
where aa = a.aa
)

-- 查看删除后的结果
select * from @aa
------解决方案--------------------
-- 送你段代码,也许用得着的.

declare @user table (user_name varchar(20) , join_dt datetime)

insert into @user (user_name , join_dt)
select '张一 ' , '2007-01-01 01:30:00 ' union all
select '张二 ' , '2007-01-01 01:50:00 ' union all
select '张三 ' , '2007-01-01 02:30:00 ' union all
select '张四 ' , '2007-01-01 21:30:00 ' union all

select '李一 ' , '2007-01-02 01:30:00 ' union all
select '李二 ' , '2007-01-02 03:30:00 ' union all
select '李三 ' , '2007-01-02 07:30:00 ' union all
select '李四 ' , '2007-01-02 09:30:00 ' union all
select '李五 ' , '2007-01-02 10:30:00 '


--/查询每日前两名用户
select *
from @user a
where join_dt <= any (
select top 2 join_dt
from @user
where convert(varchar(10) , join_dt , 112) = convert(varchar(10) , a.join_dt , 112)
order by join_dt
)
------解决方案--------------------
打个广告:http://community.csdn.net/Expert/topic/5278/5278166.xml?temp=.7078516