distinct 和 order by 的矛盾

z75148885

于 2007-04-19 10:35:00 发布

阅读量412

点赞数

文章标签： CMS C C++ C#

本文介绍了一种使用SQL语句去除查询结果中重复记录的方法。通过对比不同SQL语句的效果，展示如何利用GROUP BY结合聚合函数MAX来正确地消除重复记录，从而得到每个cont_id对应的最高点击数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select distinct cont_id from cms_contentiteminfo t order by cast (t.click as int) desc

上面这句报错ORA-1791,not a SELECTED expression

select distinct cont_id ,cast (t.click as int) as cfrom cms_contentiteminfo t order by cast (t.click as int) desc

查出的结果是有重复记录的

cont_id c

1853371517
1856391003
185586985
185337967
185641937
185337899
185639832
185641832
185641727
185283649
185144605
185586588
185639564
185216547
185586508
185283492
185144422
185283421
185216378
185217369
185144346
185626309
185217303
192278254

修改为

select distinct cont_id,max(cast (t.click as int)) c from cms_contentiteminfo t group by cont_id order by c desc

查询结果

cont_id c

1853371517
1856391003
185586985
185641937
185283649
185144605
185216547
185217369
185626309
192278254
185145234
191809214
185286191
190484190
185642190
190487180
185287171
190486162
192483146
189151138
191253118
177607117
192704116
185140115