select distinct emp.eid,emp.ename,dept.did,dept.dname,cid,cname,sys.code,sys.value,dt_time from
ORG_CLASS orgclass,ORG_DEPARTMENT dept,SYS_CODE sys,ORG_EMPLOYEE emp,wfes_personnel where
orgclass.did=dept.did and sys.code=orgclass.clevel and sys.type='CLEVEL' and orgclass.eid=emp.eid and orgclass.eid='1'1上面这条语句可以执行,但我想按照order by排序,可加上"distinct"就不能用order by,为什么啊?怎么解决?
2语句里有个dt_time,是insert的时候取的sysdate,精确到了秒,我想把重复的记录去掉,但dt_time这个字段除外,是不是要用子查询?
ORG_CLASS orgclass,ORG_DEPARTMENT dept,SYS_CODE sys,ORG_EMPLOYEE emp,wfes_personnel where
orgclass.did=dept.did and sys.code=orgclass.clevel and sys.type='CLEVEL' and orgclass.eid=emp.eid and orgclass.eid='1'1上面这条语句可以执行,但我想按照order by排序,可加上"distinct"就不能用order by,为什么啊?怎么解决?
2语句里有个dt_time,是insert的时候取的sysdate,精确到了秒,我想把重复的记录去掉,但dt_time这个字段除外,是不是要用子查询?
(select emp.eid,emp.ename,dept.did,dept.dname,cid,cname,sys.code,sys.value,dt_time from
ORG_CLASS orgclass,ORG_DEPARTMENT dept,SYS_CODE sys,ORG_EMPLOYEE emp,wfes_personnel where
orgclass.did=dept.did and sys.code=orgclass.clevel and sys.type='CLEVEL' and orgclass.eid=emp.eid and orgclass.eid='1'
这这个子查询里做order by不就可以了
)
as temp
如果要order by cid,就改成:
select distinct cid,emp.eid,emp.ename,dept.did,dept.dname, ......
由于有distinct,得到的结果,和下边的顺序相同:
select distinct col1,col2,col3,... from (sub select) order by col1,col2,col3,....
所以,【子查询】里的order by就没用啦。
由于有distinct,得到的结果,和下边的顺序相同:
select distinct col1,col2,col3,... from (sub select) order by col1,col2,col3,....
所以,【子查询】里的order by就没用啦。
我说的那个子查询里面没有distinct的
感谢各位了,结贴