sql数据重复查询问题

a102470 2014-06-03 09:26:19
我想查询出一个表中不重复的数据,重复的取articleid 最大的,按照articleid 的倒叙排列,我是下面的写法,结果运行出来不对。
SELECT Publicaccounts,articleid FROM article GROUP BY Publicaccounts,articleid order by articleid desc
...全文
225 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
a102470 2014-06-03
  • 打赏
  • 举报
回复
引用 4 楼 fredrickhu 的回复:
试试:
select
 * 
from
  ( select *, ROW_NUMBER() over( order by AutoId,order by articleid desc ) row from article as s where 1=1  and not exists(select 1 from article where Publicaccounts=s.Publicaccounts and articleid>s.articleid)  ) t
where
  row between 1 and 20
加上count后该如何改呢? select count(1) from article as t where 1=1 and not exists(select 1 from article where Publicaccounts=t.Publicaccounts and articleid>t.articleid) order by articleid desc ;
--小F-- 2014-06-03
  • 打赏
  • 举报
回复
试试:
select
 * 
from
  ( select *, ROW_NUMBER() over( order by AutoId,order by articleid desc ) row from article as s where 1=1  and not exists(select 1 from article where Publicaccounts=s.Publicaccounts and articleid>s.articleid)  ) t
where
  row between 1 and 20
a102470 2014-06-03
  • 打赏
  • 举报
回复
引用 2 楼 fredrickhu 的回复:
select * from article  as t where not exists(select 1 from article where Publicaccounts=t.Publicaccounts and articleid>t.articleid)  order by articleid desc
那个方法挺好用,但是我后来修改成这样了,报“除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。”这个错误,请问该如何修改呀? select * from ( select *, ROW_NUMBER() over( order by AutoId ) row from article as s where 1=1 and not exists(select 1 from article where Publicaccounts=s.Publicaccounts and articleid>s.articleid) order by articleid desc) t where row between 1 and 20
--小F-- 2014-06-03
  • 打赏
  • 举报
回复
select * from article  as t where not exists(select 1 from article where Publicaccounts=t.Publicaccounts and articleid>t.articleid)  order by articleid desc
  • 打赏
  • 举报
回复

SELECT Publicaccounts,
		articleid=MAX(articleid)  
FROM article 
GROUP BY Publicaccounts 
order by articleid  desc

34,571

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧