一个查询sql

jz1979 2008-06-02 02:32:38

有这样的数据:
notice_id notice_order(char(2))
1
2 1
3 3
4 2
5
查询结果后显示成这样的
notice_id notice_order
2 1
4 2
3 3
5
1
order不为空的,进行正排,列在上面...order为空的放在下面,按notice_id倒排





(select NOTICE_ID,NOTICE_ORDER
from NOTICE
where NOTICE_ORDER is not null AND NOTICE_ORDER<>''
ORDER BY NOTICE_ORDER ASC
)


union all

(select NOTICE_ID,NOTICE_ORDER
from NOTICE
where NOTICE_ORDER is null OR NOTICE_ORDER=''
ORDER BY NOTICE_ID DESC
)




我写的sql,有order的不会正排
...全文
178 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 22 楼 shili150 的回复:]
引用 21 楼 shili150 的回复:
select * from (select * from table2 where notice_order <>'' order by notice_order asc)t union all select * from(select *
from table2 where notice_order='' order by notice_id desc)t

要是不对的话 ,我估计你得重装数据库了



你的表名
[/Quote]


你的这个也正确,多谢,厉害呀!!!!
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 18 楼 jason_kou 的回复:]
SQL codeselect NOTICE_ID ,NOTICE_ORDER from
(
(select * from
(
select * from NOTICE where NOTICE_ORDER is not null AND NOTICE_ORDER<>''
order by NOTICE_ORDER ) as a
)
union all
(select * from
(
select * from NOTICE where NOTICE_ORDER is null OR NOTICE_ORDER=''
order by NOTICE_ID desc ) as b
)
) as c;
[/Quote]

你的这个正确,多谢,厉害呀
jz1979 2008-06-02
  • 打赏
  • 举报
回复
我试一下
shili150 2008-06-02
  • 打赏
  • 举报
回复
18楼的兄弟也是正确的
shili150 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 21 楼 shili150 的回复:]
select * from (select * from table2 where notice_order <>'' order by notice_order asc)t union all select * from(select *
from table2 where notice_order='' order by notice_id desc)t

要是不对的话 ,我估计你得重装数据库了
[/Quote]


你的表名
shili150 2008-06-02
  • 打赏
  • 举报
回复
select * from (select * from table2 where notice_order <>'' order by notice_order asc)t union all select * from(select *
from table2 where notice_order='' order by notice_id desc)t

要是不对的话 ,我估计你得重装数据库了
jason_kou 2008-06-02
  • 打赏
  • 举报
回复
NND,下午要去华科笔试,现在浑身都不舒服。。跑那么远。。郁闷啊
xql80329 2008-06-02
  • 打赏
  • 举报
回复
我对LZ的达到的目的 不理解.
jason_kou 2008-06-02
  • 打赏
  • 举报
回复
select NOTICE_ID ,NOTICE_ORDER from 
(
(select * from
(
select * from NOTICE where NOTICE_ORDER is not null AND NOTICE_ORDER<>''
order by NOTICE_ORDER ) as a
)
union all
(select * from
(
select * from NOTICE where NOTICE_ORDER is null OR NOTICE_ORDER=''
order by NOTICE_ID desc ) as b
)
) as c;
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 16 楼 numen_wlm 的回复:]
晕,我用的Oracle测试的
[/Quote]


select NOTICE_ID ,NOTICE_ORDER from
(
(select * from NOTICE
where NOTICE_ORDER is not null AND NOTICE_ORDER<>''
order by NOTICE_ORDER )
union all
(select * from
(
select * from NOTICE where NOTICE_ORDER is null OR NOTICE_ORDER=''
order by NOTICE_ID desc ) as A
)
) as B


我把你写的SQL稍改了下,可是也没有对order正排
numen_wlm 2008-06-02
  • 打赏
  • 举报
回复
晕,我用的Oracle测试的
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 14 楼 numen_wlm 的回复:]
引用 12 楼 jz1979 的回复:
引用 9 楼 numen_wlm 的回复:
select * from (select * from NOTICE where notice_order is not null order by notice_order) union all select * from(select * from NOTICE where notice_order is null order by notice_id desc)


不行,错误:Incorrect usage of UNION and ORDER BY

不应该啊,我测试了可以的啊
[/Quote]

我用的是mysql5.0
numen_wlm 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 12 楼 jz1979 的回复:]
引用 9 楼 numen_wlm 的回复:
select * from (select * from NOTICE where notice_order is not null order by notice_order) union all select * from(select * from NOTICE where notice_order is null order by notice_id desc)


不行,错误:Incorrect usage of UNION and ORDER BY
[/Quote]
不应该啊,我测试了可以的啊
jason_kou 2008-06-02
  • 打赏
  • 举报
回复

看错了,不好意思。。
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 numen_wlm 的回复:]
select * from (select * from NOTICE where notice_order is not null order by notice_order) union all select * from(select * from NOTICE where notice_order is null order by notice_id desc)
[/Quote]

不行,错误:Incorrect usage of UNION and ORDER BY
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 jason_kou 的回复:]
SQL code(select NOTICE_ID,NOTICE_ORDER
from NOTICE
where NOTICE_ORDER is not null AND NOTICE_ORDER<>''
ORDER BY NOTICE_ORDER
)


union all

(select NOTICE_ID,NOTICE_ORDER
from NOTICE
where NOTICE_ORDER is null OR NOTICE_ORDER=''
ORDER BY NOTICE_ID DESC
)
ORDER BY NOTICE_ORDER DESC
[/Quote]


不行,结果是对order倒排了。。。
jason_kou 2008-06-02
  • 打赏
  • 举报
回复
(select NOTICE_ID,NOTICE_ORDER
from NOTICE
where NOTICE_ORDER is not null AND NOTICE_ORDER<>''
ORDER BY NOTICE_ORDER
)


union all

(select NOTICE_ID,NOTICE_ORDER
from NOTICE
where NOTICE_ORDER is null OR NOTICE_ORDER=''
ORDER BY NOTICE_ID DESC
)
ORDER BY NOTICE_ORDER DESC

numen_wlm 2008-06-02
  • 打赏
  • 举报
回复
select * from (select * from NOTICE where notice_order is not null order by notice_order) union all select * from(select * from NOTICE where notice_order is null order by notice_id desc)
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 FL1429 的回复:]
ORDER BY NOTICE_ORDER ASC

把NOTICE_ORDER 改成NOTICE_ID

因为你的NOTICE_ORDER 是char型的。。怎么能正排那。。。

你的NOTICE_ID是int型的。。可以的~!


给分吧~!
[/Quote]


可是我要对order排序,而不是针对id
jz1979 2008-06-02
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 laorer 的回复:]
select NOTICE_ID,NOTICE_ORDER
from NOTICE
ORDER BY NOTICE_ORDER ASC,notice_id des ;
试试,
[/Quote]


不行
加载更多回复(6)

62,612

社区成员

发帖
与我相关
我的任务
社区描述
Java 2 Standard Edition
社区管理员
  • Java SE
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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