不知道楼主有没有注意到这样一个问题(注意这个问题能有助于对我上面的话的理解): create table #t(age int)insert #t select 35 union select 25 union select 30select * from #t--orcreate table #t(age int)insert #t select 30 union select 35 union select 25select * from #t--orcreate table #t(age int)insert #t select 25 union select 35 union select 30select * from #t--上面的语句不管怎么排法,所得到的结果都只有一个:/* age ----------- 25 30 35(3 行受影响)*/ 你可以自己测试,反正我测得的结果就是这样.由此可知,不按某种方式进行排序,就对行的排列确定先后顺序,是不靠谱的.
用union all selectcreate table #t(age int)insert #t select 25 union all select 35 union all select 30select * from #t /* 25 35 30 */
你的表,最好有一个自增列,这样,数据一行行从txt文件读来插入的时候,就有一个插入顺序,你可以根据这个插入顺序来判断读来的数据是否按升序或降序排列,判断语句如:create table #t(id int identity(1,1),age int) insert #t select 25 union all select 35 union all select 30 go select case when exists(select 1 from #t a where exists(select 1 from #t where id>a.id and age<a.age)) then '不合法' else '合法' end /* ------ 不合法(1 行受影响)*/ go drop table #t go create table #t(id int identity(1,1),age int) insert #t select 25 union all select 30 union all select 35 go select case when exists(select 1 from #t a where exists(select 1 from #t where id>a.id and age<a.age)) then '不合法' else '合法' end /* ------ 合法(1 行受影响)*/ go drop table #t
create table t1 ( id int ) insert into t1 select 20 union all select 25 union all select 15 select * from t1;with aaa as ( select ROW_NUMBER() over(order by getdate()) as row,* from t1 ) ,bbb as (select b.id-a.id as id from aaa as a inner join aaa as b on a.row=b.row-1) select * into #t1 from bbb where id<0 if exists (select 1 from #t1) begin print '不合法' end else begin print '合法' end只是个思路,但正如1楼晴天所说,MSSQL基于集合,存储的数据并不存在一个顺序的问题。
你只列出了一个列,又要不按某种排序方式来确定它是否排列合法,本身就是不通的逻辑.这个表的其他列是什么样的呢?
create table #t(age int)insert #t select 35
union select 25
union select 30select * from #t--orcreate table #t(age int)insert #t select 30
union select 35
union select 25select * from #t--orcreate table #t(age int)insert #t select 25
union select 35
union select 30select * from #t--上面的语句不管怎么排法,所得到的结果都只有一个:/*
age
-----------
25
30
35(3 行受影响)*/
你可以自己测试,反正我测得的结果就是这样.由此可知,不按某种方式进行排序,就对行的排列确定先后顺序,是不靠谱的.
union all select 35
union all select 30select * from #t
/*
25
35
30
*/
你的表,最好有一个自增列,这样,数据一行行从txt文件读来插入的时候,就有一个插入顺序,你可以根据这个插入顺序来判断读来的数据是否按升序或降序排列,判断语句如:create table #t(id int identity(1,1),age int)
insert #t select 25
union all select 35
union all select 30
go
select case when exists(select 1 from #t a where exists(select 1 from #t where id>a.id and age<a.age)) then '不合法' else '合法' end
/*
------
不合法(1 行受影响)*/
go
drop table #t
go
create table #t(id int identity(1,1),age int)
insert #t select 25
union all select 30
union all select 35
go
select case when exists(select 1 from #t a where exists(select 1 from #t where id>a.id and age<a.age)) then '不合法' else '合法' end
/*
------
合法(1 行受影响)*/
go
drop table #t
create table t1
(
id int
)
insert into t1
select 20 union all
select 25 union all
select 15
select * from t1;with aaa as
(
select ROW_NUMBER() over(order by getdate()) as row,* from t1
)
,bbb as
(select b.id-a.id as id from aaa as a inner join aaa as b on a.row=b.row-1)
select * into #t1 from bbb where id<0
if exists (select 1 from #t1)
begin
print '不合法'
end
else
begin
print '合法'
end只是个思路,但正如1楼晴天所说,MSSQL基于集合,存储的数据并不存在一个顺序的问题。