distinct 和 order by 的 矛盾

最新推荐文章于 2024-03-01 18:02:17 发布
最新推荐文章于 2024-03-01 18:02:17 发布
阅读量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

cont-id重复的记录就可以去掉了

z75148885
关注
distinct 和order by 冲突解决方案
Xhm1064的博客
07-02 3449
在新版的mysql5.7的版本中，如果DISTINCT和order by一起使用将会报3065错误，sql语句无法执行。最新的mysql5.7版本语法比之前5.6版本语法更加严格导致的。DISTINCT和order by都会对数据进行排序操作，所以会产生冲突。 SELECT DISTINCT a.title,a.tax_no FROM order_invoice a LEFT JOIN o...
distinct和order by冲突
立业的博客
08-03 5939
当select子句使用distinct关键字时，可能会与order by子句冲突，从而产生如下错误 错误：ORA-01791: not a SELECTed expression 根据sql语句的执行顺序，distinct的执行顺序优先于order by，因此distinct对select的字段去重后，order by只能在distinct后返回的结果集进行排序 因此order by 后的字段如...
distinct 与 order by冲突问题
weixin_56746335的博客
02-14 269
distinct 与 order by冲突问题解决。
ORDER BY 子句与 DISTINCT冲突
xing_sky的专栏
11-07 3491
在SQL中。如果使用SELECT DISTINCT(A) FROM TABLE ORDER BY 。。。时，后面ORDER BY 后的字段名必须在前面选择时出现，比如 select distinct(D1) from shop order by D1  这样才不会出现冲突，如果是select distinct(D1) from shop order by D2 则会发生冲突； 还有就是如果ORD
DISTINCT 与ORDER BY 同时存在，并对排序字段用collate指定排序规则后报错问题
chen2017sheng的博客
03-01 1964
DISTINCT 与ORDER BY同时存在，并对排序字段用collate指定排序规则后报错问题
使用 DISTINCT 去重时, ORDER BY子句的排序字段必须 出现在聚合函数参数列表中
03-20
MySQL 要求：若在 `GROUP_CONCAT` 中同时使用 `DISTINCT` 和 `ORDER BY`，则 **`ORDER BY` 的字段必须是 `DISTINCT` 去重的字段的子集**。 --- ### **具体案例** 假设原查询如下： ```sql GROUP_CONCAT(DISTINCT ...
SQL: SELECT DISTINCT DATE(create_time) AS date FROM tj_personnel_attendance_record WHERE project_id = ? AND id_card_no = ? AND create_time BETWEEN ? AND ? GROUP BY create_time ORDER BY create_time DESC ### Cause: java.sql.SQLException: errCode = 2, detailMessage = cannot combine SELECT DISTINCT with aggregate functions or GROUP BY报错
03-08
特别是在某些数据库系统中，比如MySQL，DISTINCT和GROUP BY一起使用时可能会有冲突，尤其是在分组字段和选择字段不一致的情况下。 接下来，我需要分析用户的SQL结构。用户的目标是获取唯一的日期列表，按时间降序...
SELECT DISTINCT (CASE WHEN '${C3}' = 1 THEN T1.FLOW_SEQ ELSE null END) FLOW_SEQ FROM (SELECT L2.FLOW_SEQ FROM RPTDW.FLOW_ACTIVE_MASTER L2 WHERE L2.PRODUCT_NAME IN ('${B3}') OR L2.CAPABILITY IN ('${C3}') ORDER BY FLOW_SEQ )T1, (SELECT L2.FLOW_SEQ FROM RPTDW.FLOW_ACTIVE_MASTER L2 WHERE L2.CAPABILITY IN ('${C3}') AND L2.PRODUCT_NAME IN ('${B3}') ORDER BY FLOW_SEQ)T2 分析代码，为什么${C3}=1时，仍然为null值
最新发布
07-02
```sqlSELECT DISTINCT(CASEWHEN '${C3}'= '1'THEN T1.FLOW_SEQELSE nullEND)AS FLOW_SEQFROM(SELECT L2.FLOW_SEQFROM RPTDW.FLOW_ACTIVE_MASTER L2WHEREL2.PRODUCT_NAMEIN ('${B3}')OR L2.CAPABILITYIN ('${C3}')...
select SUM(t1.Issue_Num) Issue_Num, t1.Short_Name, t1.Subsidiary, dateadd(month, datediff(month, 0,t1.Apply_Time), 0) as Start_Date, dateadd(month, datediff(month, 0, dateadd(month, 1, t1.Apply_Time)), -1) as End_Date, cast("Year" as varchar)+'年'+cast("Month" as varchar)+'月报' as '报告期', case when t2.Issue_NumT = 0 then 0 else (t1.Issue_Num - t2.Issue_NumT)/ t2.Issue_NumT *100 end as YOY_Number, '同比' as YOY, t1.SignY, null Issue_Reson, distinct t1.Responsibility_Judgment from (select Complaint.Status, Order_Num, Apply_Time, count(distinct ComplaintList.Responsibility_Judgment) as Issue_Num, ComplaintList.Subsidiary, ComplaintList.Responsibility_Judgment, ComplaintList.SignY, sub.Short_Name, YEAR(Complaint.Apply_Time) Year, MONTH(Complaint.Apply_Time) Month from [dbo].[IMP_MidDataCustomerComplaint] as Complaint left join [dbo].[IMP_MidDataComplaintList] as ComplaintList on Complaint.Serve_Order = ComplaintList.Serve_Order left join Mid_witsky.dbo.Base_Subsidiary as sub on sub.Code = ComplaintList.Subsidiary where --ComplaintList.Responsibility_Judgment = 'DT001' --and Complaint.Status = 'OP002' Complaint.Del_Flag = 0 and YEAR(Apply_Time) = 2025 and MONTH(Apply_Time) = 2 --and ComplaintList.Responsibility_Judgment = '' and (Subsidiary is NULL or Subsidiary = '') group by Complaint.Status, Order_Num, Apply_Time, ComplaintList.Subsidiary, ComplaintList.Responsibility_Judgment, ComplaintList.SignY, sub.Short_Name, YEAR(Complaint.Apply_Time) , MONTH(Complaint.Apply_Time))t1 left join (select Complaint.Status as StatusT, Order_Num as Order_NumT, Apply_Time as Apply_TimeT, count(distinct Complaint.Serve_Order) as Issue_NumT, ComplaintList.Subsidiary as SubsidiaryT, ComplaintList.Responsibility_Judgment as RJ, ComplaintList.SignY as SignYT, sub.Short_Name as NameT, YEAR(Complaint.Apply_Time) YearT, MONTH(Complaint.Apply_Time) MonthT from [dbo].[IMP_MidDataCustomerComplaint] as Complaint left join [dbo].[IMP_MidDataComplaintList] as ComplaintList on Complaint.Serve_Order = ComplaintList.Serve_Order left join Mid_witsky.dbo.Base_Subsidiary as sub on sub.Code = ComplaintList.Subsidiary where --ComplaintList.Responsibility_Judgment = 'DT001' --and Complaint.Status = 'OP002' Complaint.Del_Flag = 0 --and ComplaintList.Responsibility_Judgment = '' and Subsidiary is NULL or Subsidiary = '' group by Complaint.Status, Order_Num, Apply_Time, ComplaintList.Subsidiary, ComplaintList.Responsibility_Judgment, ComplaintList.SignY, sub.Short_Name, YEAR(Complaint.Apply_Time) , MONTH(Complaint.Apply_Time))t2 on t1.year - 1 = t2.YearT and t1.month = t2.MonthT and t1.Subsidiary = t2.SubsidiaryT where t1.Month in('2') and t1.SignY = 'CA002' and t1.Year > ( case when '3年' = '3年' then cast('2025' as INTEGER) -3 when '3年' = '5年' then cast('2025' as INTEGER) -5 when '3年' = '10年' then cast('2025' as INTEGER) -10 when '3年' = '15年' then cast('2025' as INTEGER) -15 else cast('2025' as INTEGER) -20 end ) group by t1.Short_Name, t1.Subsidiary, dateadd(month, datediff(month, 0,t1.Apply_Time), 0), dateadd(month, datediff(month, 0, dateadd(month, 1, t1.Apply_Time)), -1) , cast("Year" as varchar)+'年'+cast("Month" as varchar)+'月报' , case when t2.Issue_NumT = 0 then 0 else (t1.Issue_Num - t2.Issue_NumT)/ t2.Issue_NumT *100 end , t1.SignY, t1.Year, t1.Month, t1.Responsibility_Judgment order by Year desc,Month desc 为什么会在添加distinct t1.Responsibility_Judgment后报错
03-09
原查询已通过`GROUP BY`对多列分组（如`Short_Name`, `Subsidiary`等），此时再对`Responsibility_Judgment`单独去重会导致逻辑矛盾： - `GROUP BY`要求按指定列分组后聚合（如`SUM`） - `DISTINCT t1....
select DISTINCT a.basic_prod_offer_inst_id,a.yd_service_nbr,a.kd_prd_inst_id from xxzx.tb_tzfx_zc_gzgw_pd a group by a.basic_prod_offer_inst_id;
04-02
首先，注意到用户同时使用了DISTINCT和GROUP BY。这里可能存在问题。因为在GROUP BY子句中，用户只按basic_prod_offer_inst_id分组，但SELECT列表中还有yd_service_nbr和kd_prd_inst_id这两个字段，这两个字段没有在...
解决Sql中DIstinct与Order By共同使用的冲突问题
weixin_30783629的博客
06-02 394
1.需求场景： 　　需要把最新更新文章的前五名作者展示出来。 2.解决问题第一步： 　　 select top 5 creator from table order by updateDate desc 结果：显示五条记录，但是名字是重复的。 3.解决问题第二步： 加Distinct select distinct top 5 creator from ...
DISTINCT和ORDER BY
小灰灰的部落
01-15 1017
当指定了DISTINCT以后，ORDER BY子句就被限制为，只能选取在SELECT列表中出现的那些元素。 --FALSE SELECT DISTINCT country FROM HR.Employees ORDER BY empid; --TRUE SELECT DISTINCT country FROM HR.Employees ORDER
DISTINCT与ORDER BY
我的博客
10-06 558
select DISTINCT id from user Order By case ; 该语句SQL语名,如果加上了DISTINCT就报错, 注意：    DISTINCT与ORDER BY一般不要一起使用，用Group By调节；
distinct 和 order by
find_zhi的专栏
04-21 848
问题描述：有两个sql，我以为查询结果会一样，但是结果却是天差地别：第一条语句：select  distinct(ic.companyId) as companyId  from industry_company ic join funding f on f.companyId=ic.companyId join company c on c.id = ic.companyId where  (c...
SQL中distinct与order by
l77yq的专栏
06-17 281
----------------------------------------------------------- 错误 [code="java"]select distinct ga.id from GeneralGranted pro, GeneralMember gm left join gm.department d left join gm.institute i...
distinct 与order by 一起用
dinglongpei8983的博客
08-31 148
distinct 后面不要直接跟Order by , 如果要用在子查询用用order by . 转载于:https://www.cnblogs.com/yanjunwu/p/4773528.html
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值