trySelect * From(
select TOP 100 Percent dabh from A where dabh is null order by dabh ) A
union All
Select * From(
select TOP 100 Percent dabh from A where not dabh is null and dabh not like '[a-z]%' order by cast(dabh as int) ) A
union All
Select * From(
select TOP 100 Percent dabh from A where not dabh is null and dabh like '[a-z]%' order by dabh) A
select TOP 100 Percent dabh from A where dabh is null order by dabh ) A
union All
Select * From(
select TOP 100 Percent dabh from A where not dabh is null and dabh not like '[a-z]%' order by cast(dabh as int) ) A
union All
Select * From(
select TOP 100 Percent dabh from A where not dabh is null and dabh like '[a-z]%' order by dabh) A
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。把所有*,替换成dabh
还是返回:
服务器: 消息 104,级别 15,状态 1,行 1
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。我的初衷是想用UNION连接,还想使用order by cast(dabh as int),用一句实现我的数据库系统是:SQL Server 2000
Order By (Case When dabh Is Null Then 0 Else
(Case When IsNumeric(dabh) = 1 Then Cast(dabh As Int) Else 99999 End) End)
Create Table A
(dabh varchar(50))
Insert A Select null
Union All Select '0'
Union All Select '1'
Union All Select '10'
Union All Select '101'
Union All Select '12'
Union All Select '20'
Union All Select 'a1'
Union All Select 'b1'
GO
--測試
Select * From A
Order By (Case When dabh Is Null Then 0 Else
(Case When IsNumeric(dabh) = 1 Then Cast(dabh As Int) Else 99999 End) End)
GO
--刪除測試環境
Drop Table A
--結果
/*
dabh
NULL
0
1
10
12
20
101
a1
b1
*/
第一次来提问题就这么容易解决了,我想了很长时间,看来还得多来Csdn来学习。 有机会还得和paoluo(一天到晚游泳的鱼)高手学习。