有这样的数据:
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的不会正排
from NOTICE
ORDER BY NOTICE_ORDER ASC,notice_id des ;
试试,
给分吧~!
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
不行,错误:Incorrect usage of UNION and ORDER BY
(
(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正排
(
(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;
from table2 where notice_order='' order by notice_id desc)t要是不对的话 ,我估计你得重装数据库了