暂无图片
分享
Uncopyrightable
2021-03-01
oracle orderby 排序问题
暂无图片 10M

使用下列语句进行排序的时候c1列出现重复情况,但是selet查询结果只有c1,c2,c3,c4,c5列是没有重复情况,去掉order by关键字也没有重复情况,在order by关键中添加rowid列c1查询结果还是有重复。这种添加order by 函数查询结果出现重复是为什么?

select t0.MD_ORG as c1,
t0.MD_CURRENCY as c2,
t0.MD_GCORGTYPE as c3,
t0.MD_GCADJTYPE as c4,
t0.DEFAULT_PERIOD as c5,
t0.A01 as c6,
t0.A02 as c7,
t0.A03 as c8,
t0.A04 as c9,
t0.A05 as c10,
t0.A06 as c11,
t0.A07 as c12,
t0.A08 as c13,
t0.A09 as c14,
t0.A10 as c15,
t0.A11 as c16,
t0.A12 as c17,
t0.A13 as c18,
t0.A14 as c19,
t0.A15 as c20,
t0.A16 as c21,
t0.A17 as c22,
t0.A18 as c23,
t0.A19 as c24,
t0.A20 as c25,
t0.A21 as c26,
t0.A22 as c27,
t0.A23 as c28,
t0.A24 as c29,
t0.A25 as c30,
t0.A26 as c31,
t0.A27 as c32,
t0.A28 as c33,
t0.A29 as c34,
t0.A30 as c35,
t0.A31 as c36,
t0.A32 as c37,
t0.A33 as c38,
t0.A34 as c39,
t0.A35 as c40,
t0.A36 as c41,
t0.A37 as c42,
t0.A38 as c43,
t0.A39 as c44,
t0.A40 as c45,
t0.A41 as c46,
t0.A42 as c47,
t0.A43 as c48,
t0.A44 as c49,
t0.A45 as c50,
t0.B01 as c51,
t0.B02 as c52,
t0.B03 as c53,
t0.B04 as c54,
t0.B05 as c55,
t0.B06 as c56,
t0.B07 as c57,
t0.B08 as c58,
t0.B09 as c59,
t0.B10 as c60,
t0.B11 as c61,
t0.B12 as c62,
t0.B13 as c63,
t0.B14 as c64,
t0.B15 as c65,
t0.B16 as c66,
t0.B17 as c67,
t0.B18 as c68,
t0.B19 as c69,
t0.B20 as c70,
t0.B21 as c71,
t0.B22 as c72,
t0.B23 as c73,
t0.B24 as c74,
t0.B25 as c75,
t0.B26 as c76,
t0.B27 as c77,
t0.B28 as c78,
t0.B29 as c79,
t0.B30 as c80,
t0.B31 as c81,
t0.B32 as c82,
t0.B33 as c83,
t0.B34 as c84,
t0.B35 as c85,
t0.B36 as c86,
t0.B37 as c87,
t0.B38 as c88,
t0.B39 as c89,
t0.B40 as c90,
t0.B41 as c91,
t0.B42 as c92,
t0.B43 as c93,
t0.B44 as c94,
t0.B45 as c95,
t0.C01 as c96,
t0.C02 as c97,
t0.C03 as c98,
t0.C04 as c99,
t0.C05 as c100,
t0.C06 as c101,
t0.C07 as c102,
t0.C08 as c103,
t0.C09 as c104,
t0.C10 as c105,
t0.C11 as c106,
t0.C12 as c107,
t0.C13 as c108,
t0.C14 as c109,
t0.C15 as c110,
t0.C16 as c111,
t0.C17 as c112,
t0.C18 as c113,
t0.C19 as c114,
t0.C20 as c115,
t0.C21 as c116,
t0.C22 as c117,
t0.C23 as c118,
t0.C24 as c119,
t0.C25 as c120,
t0.C26 as c121,
t0.C27 as c122,
t0.C28 as c123,
t0.C29 as c124,
t0.C30 as c125,
t0.C31 as c126,
t0.C32 as c127,
t0.C33 as c128,
t0.C34 as c129,
t0.C35 as c130,
t0.C36 as c131,
t0.C37 as c132,
t0.C38 as c133,
t0.C39 as c134,
t0.C40 as c135,
t0.C41 as c136,
t0.C42 as c137,
t0.C43 as c138,
t0.C44 as c139,
t0.C45 as c140,
t0.C51 as c141,
t0.C52 as c142,
t0.C53 as c143,
t0.D01 as c144,
t0.D02 as c145,
t0.D03 as c146,
t0.D04 as c147,
t0.D05 as c148,
t0.D06 as c149,
t0.D07 as c150,
t0.D08 as c151,
t0.D09 as c152,
t0.D10 as c153,
t0.D11 as c154,
t0.D12 as c155,
t0.D13 as c156,
t0.D14 as c157,
t0.D15 as c158,
t0.D16 as c159,
t0.D17 as c160,
t0.D18 as c161,
t0.D19 as c162,
t0.D20 as c163,
t0.D21 as c164,
t0.D22 as c165,
t0.D23 as c166,
t0.D24 as c167,
t0.D25 as c168,
t0.D26 as c169,
t0.D27 as c170,
t0.D28 as c171,
t0.D29 as c172,
t0.D30 as c173,
t0.D31 as c174,
t0.D32 as c175,
t0.D33 as c176,
t0.D34 as c177,
t0.D35 as c178,
t0.D36 as c179,
t0.D37 as c180,
t0.D38 as c181,
t0.D39 as c182,
t0.D40 as c183,
t0.D41 as c184,
t0.D42 as c185,
t0.D43 as c186,
t0.D44 as c187,
t0.D45 as c188,
t0.D51 as c189,
t0.D52 as c190,
t0.D53 as c191,
t0.E01 as c192,
t0.E02 as c193,
t0.E03 as c194,
t0.E04 as c195,
t0.E05 as c196,
t0.E06 as c197,
t0.E07 as c198,
t0.E08 as c199,
t0.E09 as c200,
t0.E10 as c201,
t0.E11 as c202,
t0.E12 as c203,
t0.E13 as c204,
t0.E14 as c205,
t0.E15 as c206,
t0.E16 as c207,
t0.E17 as c208,
t0.E18 as c209,
t0.E19 as c210,
t0.E20 as c211,
t0.E21 as c212,
t0.E22 as c213,
t0.E23 as c214,
t0.E24 as c215,
t0.E25 as c216,
t0.E26 as c217,
t0.E27 as c218,
t0.E28 as c219,
t0.E29 as c220,
t0.E30 as c221,
t0.E31 as c222,
t0.E32 as c223,
t0.E33 as c224,
t0.E34 as c225,
t0.E35 as c226,
t0.E36 as c227,
t0.E37 as c228,
t0.E38 as c229,
t0.E39 as c230,
t0.E40 as c231,
t0.E41 as c232,
t0.E42 as c233,
t0.E43 as c234,
t0.E44 as c235,
t0.E45 as c236,
t0.E51 as c237,
t0.E52 as c238,
t0.E53 as c239,
t0.F01 as c240,
t0.F02 as c241,
t0.F03 as c242,
t0.F04 as c243,
t0.F05 as c244,
t0.F06 as c245,
t0.F07 as c246,
t0.F08 as c247,
t0.F09 as c248,
t0.F10 as c249,
t0.F11 as c250,
t0.F12 as c251,
t0.F13 as c252,
t0.F14 as c253,
t0.F15 as c254,
t0.F16 as c255,
t0.F17 as c256,
t0.F18 as c257,
t0.F19 as c258,
t0.F20 as c259,
t0.F21 as c260,
t0.F22 as c261,
t0.F23 as c262,
t0.F24 as c263,
t0.F25 as c264,
t0.F26 as c265,
t0.F27 as c266,
t0.F28 as c267,
t0.F29 as c268,
t0.F30 as c269,
t0.F31 as c270,
t0.F32 as c271,
t0.F33 as c272,
t0.F34 as c273,
t0.F35 as c274,
t0.F36 as c275,
t0.F37 as c276,
t0.F38 as c277,
t0.F39 as c278,
t0.F40 as c279,
t0.F41 as c280,
t0.F42 as c281,
t0.F43 as c282,
t0.F44 as c283,
t0.F45 as c284,
t0.F51 as c285,
t0.F52 as c286,
t0.F53 as c287,
t0.G51 as c288,
t0.G52 as c289,
t0.G53 as c290,
t0.H51 as c291,
t0.H52 as c292,
t0.H53 as c293,
t0.DEFAULT_PERIOD as c294,
t0.MD_CURRENCY as c295,
t0.MD_GCADJTYPE as c296,
t0.MD_GCORGTYPE as c297,
t0.MD_ORG as c298
from QVQ7_GZQYJG02 t0
where t0.DEFAULT_PERIOD = ‘2021Y0001’
and t0.MD_CURRENCY = ‘CNY’
and t0.MD_GCADJTYPE = ‘BEFOREADJ’
and t0.MD_GCORGTYPE = ‘MD_ORG_CORPORATE’
and t0.MD_ORG in (‘91110000100003516F1’,‘91110000717828315T1’,‘91110105055637033T1’,’#8520000GX1148636N1’,‘9111010866990537031’,‘9146000020128085461’,‘91360700117662542G1’,‘91110108791610429B1’,‘91110102201808057N1’,‘91110102201808057N2’,’#8520000GX1148636N3’,‘91110000100003559W9’,’#B000000100000016P9’,’#B000000100000024J9’,‘91110000717828315T0’,‘91360700117662542G0’,’#B000000100000008W9’,‘9111010866990537030’,‘91110113MA01818M5U0’,‘91340100335626339Y0’,‘91110102633781927F0’,‘91440300MA5F1DHGXP0’,‘91110102201808057N9’,‘9142038118221408470’,‘9133010374945678XA0’,’#8520000GX7689820N3’,‘91320292MA20WCM59R0’,‘91440300MA5DDLLR0J9’,‘91620102750940472A0’,‘91440101MA59HH7E2N0’,‘91640000750834215T0’,‘91440101072117574A0’,‘9142060017931392XB0’,’#8520000GX5592072T3’,‘91110000575154684H0’,‘9142010373791502990’,‘91110113MA01GBYM1J0’,‘91110000306746323E2’,‘91460100324155738C0’,‘9133010605672880X20’,‘91330102MA2GNMNM790’,‘91330102MA27YN43580’,’#8520000GX1405137G3’,‘91460000399253922G9’,‘91110105551351683E0’,‘91110000717843582F9’,‘9142010030379365050’,‘91530102727316305X0’,‘91370103MA3CE9P97Q0’,‘91110108MA01C0J55L2’,‘91460000734083503Y0’,‘91110108791610429B9’,‘9153011130959472440’,‘91640100MA76C7KU2W0’,‘9141010235614048560’,‘91110106MA002JNW8H0’,‘9146000074778441389’,’#8520000G1752350993’,‘91460000721272719F9’,‘91110000164103559L9’,‘91110105055637033T9’,’#8520000GX1148636N9’,‘91110000710931168A9’,‘91370211MA3R28AP8M0’,‘9111000071092314199’,‘91110000MA004W0L880’,‘91310115583480869C0’,’#8520000G18211208D9’,‘9111010510001587579’,‘91360700117662542G9’,‘91110101MA01RKKW970’,‘91110000MA01RY7G300’,‘91110000717828315T9’,‘91440300MA5DDLLR0J1’,‘91440300MA5DDLLR0J0’,‘91440300MA5DHX6U4H2’,‘91460000100018005K9’,‘91110000717843582F1’,‘91110000717843582F2’,‘91120118MA05LN8Q212’,‘91120118MA05KXJH072’,‘91120118MA05KJLY4W9’,‘91110000164103559L1’,‘91110000164103559L0’,‘91510100MA64QQ5B6J2’,‘91110000MA004W3X590’,‘91110000710931168A1’,‘91110000710931168A0’,‘91110000MA0045ENXL0’,‘91110108600029579B0’,‘91110000100001924J0’,‘91110101101221865J0’,‘9111010866990537039’,‘9132011809400789499’,‘9111010878860758759’,‘91110000400007033T9’,‘91110000100003516F9’,‘91310000132208321Y9’,‘9132011809400789491’,‘9132011809400789490’,‘9132011830242764090’,‘91320118302427587D0’,‘9132011830242754400’,‘91320118302386420G0’,‘91320118302386543H0’,‘9132011830238657840’,‘91320118302386463X0’,‘9132011830242751XA0’,‘9132011830278757490’,‘91320118302787507B0’,‘9132011830278751560’,‘9111010878860758751’,‘9111010878860758750’,‘9111010878172058790’,‘91310116MA1JC84RX00’,‘91110000400007033T1’,‘91110000400007033T0’,‘91110108675077524C0’,‘91310000132208321Y1’,‘91310000132208321Y0’,‘91440116769544031W0’,‘9111000071092314191’,‘9111000071092314190’,‘91110102633713668X0’,‘9111010875603320870’,‘9111010810190771X50’,‘91110106099776879Q0’,‘9111010258581591730’,’#8520000G05607895Q3’,‘91110102MA01NB07070’,’#8520000G18211208D1’,’#8520000G18211208D3’,’#8520000G2467341773’,‘91460000100018005K1’,‘91460000100018005K0’,‘9133000073528055X70’,‘91460000721276939R9’,‘9146000020128085469’,‘91460000721276939R1’,‘91460000721276939R0’,‘91410105749221761C0’,‘9132010474821168430’,‘91460000721272719F1’,‘91460000721272719F0’,‘9146000072124228800’,‘9143010574837269950’,‘9121010274079765XA0’,‘91510000746948463C0’,‘91120118MA05KJLY4W1’,‘91120118MA05KJLY4W2’,‘91110108MA01MHTH880’)
order by c1,c2,c3,c4,c5,rowid

一级标题

收藏
分享
13条回答
默认
最新
赵勇

“使用下列语句进行排序的时候c1列出现重复情况,但是selet查询结果只有c1,c2,c3,c4,c5列是没有重复情况”
从这个描述看,应该只有结果集变化了,才会出现这种情况。我的建议是将两次查询的结果临时保存到中间表,然后对结果集做对比。先确定结果集是相同的。如果结果集相同,不太可能出现一个结果集中有重复值,另一个结果集无重复值。

暂无图片 评论
暂无图片 有用 0
打赏 0
暂无图片
文成

结果查询的图片传上来看看
单表查询 跟 order by 子句没关系

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

企业微信截图_16145685332452.png

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

企业微信截图_16145753982122.png

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

企业微信截图_16145756392865.png

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

企业微信截图_16145757503465.png

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

操作截图已上传,但个人感觉是客户端使用科学计数法的问题

暂无图片 评论
暂无图片 有用 0
打赏 0
你好我是李白

你这个c1列是主键或者有唯一约束?数据本身是不重复的是吗?

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

C1列不是唯一的,有重复值,想通过增加rowid列进行排序,还是有重复结果

下午还发现一个现象当select条件只有c1,c2,c3,c4,c5列的时候就不会有重复列

难道是c6以后的列参与排序?

暂无图片 评论
暂无图片 有用 0
打赏 0
你好我是李白

讲道理,order by是按你写的顺序排序的吧,c1有重复的情况系,加rowid不加rowid,c1这列都有重复值吧。
你是想达到一种什么效果嘞?不让c1重复显示?

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable

是的,c1列不出现重复的排列

添加过distinct去重,还是不行,难道需要需要使用row_number()?

暂无图片 评论
暂无图片 有用 0
打赏 0
你好我是李白

因为你还要了除c1以外其他列,所有列组成了完整的一行行数据,如果你只是要c1不重复,那也就意味着需要同时排除掉其他列的某些值了。
例如Oracle的分析函数row_number()…over() ,以c1分组,然后只取每组第一行,才能达到去重c1的效果,当然还有一些其他函数也能达到相同效果。

暂无图片 评论
暂无图片 有用 0
打赏 0
Uncopyrightable
问题已关闭: 问题已经得到解决
暂无图片 评论
暂无图片 有用 0
打赏 0
回答交流
Markdown


请输入正文
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签,敬请期待~~
暂无图片墨值悬赏