删除一个表中同个资料最大的那个值,快来帮帮我!
例如表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 (
aaint ,
bbint ,
ccchar(8)
)
insertinto @aa (aa , bb , cc)
select1 , 255 , '20061212 ' union all
select1 , 15 , '20061210 ' union all
select2 , 33 , '20060612 ' union all
select2 , 11 , '20060101 '
--/进行删除操作以前,必须进行 select 操作,先看清楚要删的是否正确.
select*
from@aa a
wherecc = (
selectmax(cc)
from@aa
whereaa = a.aa
)
-- 经上一步确认无误后再执行下边一步,进行删除.
deletea
from@aa a
wherecc = (
selectmax(cc)
from@aa
whereaa = a.aa
)
-- 查看删除后的结果
select* from @aa
[解决办法]
-- 送你段代码,也许用得着的.
declare@user table (user_name varchar(20) , join_dt datetime)
insertinto @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
wherejoin_dt <= any (
selecttop 2 join_dt
from@user
whereconvert(varchar(10) , join_dt , 112) = convert(varchar(10) , a.join_dt , 112)
orderby join_dt
)
[解决办法]
打个广告:http://community.csdn.net/Expert/topic/5278/5278166.xml?temp=.7078516
