删除一个表中同个资料最大的那个值,快来帮帮我!
例如表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