我要查询出shop视图里的5条记录,但是里面的Expr4字段的数据有重复的,怎么查询出不重复的呢?我用了select top 5 contid,usernumber,distinct(expr4) from shop order by addtime desc 是不对的 应该怎么写呀? 请教各位呀.!
select top 5 contid,usernumber,expr4 from shop t where not exists(select 1 from shop where expr4=t.expr4 and addtime>t.addtime) order by addtime desc
select top 5 contid,usernumber,expr4 from shop t where addtime=(select max(addtime) from shop where expr4=t.expr4) order by addtime desc
select top 5 contid,usernumber,expr4 from shop group by contid,usernumber,expr4 order by addtime desc上2楼的方法挺好
select top 5 contid,usernumber,expr4 from( select contid,usernumber,expr4,ROW_NUMBER() over(partition by contid,usernumber order by addtime desc ) as rn from shop ) a where rn=1
select top 5 t.* from shop t where addtime = (select max(addtime) from shop where expr4 = t.expr4) order by addtime desc select top 5 t.* from shop t where not exists (select 1 from shop where expr4 = t.expr4 and addtime > t.addtime) order by addtime descselect top 5 t.* from shop t where addtime = (select min(addtime) from shop where expr4 = t.expr4) order by addtime desc select top 5 t.* from shop t where not exists (select 1 from shop where expr4 = t.expr4 and addtime < t.addtime) order by addtime desc
from shop t
where not exists(select 1 from shop where expr4=t.expr4 and addtime>t.addtime)
order by addtime desc
top 5 contid,usernumber,expr4
from
shop t
where
addtime=(select max(addtime) from shop where expr4=t.expr4)
order by
addtime desc
from shop
group by contid,usernumber,expr4
order by addtime desc上2楼的方法挺好
select contid,usernumber,expr4,ROW_NUMBER() over(partition by contid,usernumber order by addtime desc ) as rn from shop
) a where rn=1
select top 5 t.* from shop t where not exists (select 1 from shop where expr4 = t.expr4 and addtime > t.addtime) order by addtime descselect top 5 t.* from shop t where addtime = (select min(addtime) from shop where expr4 = t.expr4) order by addtime desc
select top 5 t.* from shop t where not exists (select 1 from shop where expr4 = t.expr4 and addtime < t.addtime) order by addtime desc