我想实现输出 一个表时,按日期字段倒序后,带有行号1,2,3,4...
如表原记录是:
日期 单号
2008-05-01 001
2008-05-20 010
2008-05-08 003
...我想输出时按以下格式:
序号 日期 单号
1 2008-05-20 010
2 2008-05-08 003
3 2008-05-01 001
...用以下语句生成的行号不符合我的要求,序号乱了
select identity(int,1,1) as id,* into #temp1 from TableName order by 日期 desc请问是什么问题?
如表原记录是:
日期 单号
2008-05-01 001
2008-05-20 010
2008-05-08 003
...我想输出时按以下格式:
序号 日期 单号
1 2008-05-20 010
2 2008-05-08 003
3 2008-05-01 001
...用以下语句生成的行号不符合我的要求,序号乱了
select identity(int,1,1) as id,* into #temp1 from TableName order by 日期 desc请问是什么问题?
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (日期 datetime,单号 varchar(3))
insert into #T1
select '2008-05-01','001' union all
select '2008-05-20','010' union all
select '2008-05-08','003'goselect identity(int,1,1) as id,* into #temp1 from #T1 order by 日期 desc select * from #temp1go
drop table #T1,#temp1/*
id 日期 单号
----------- ------------------------------------------------------ ----
1 2008-05-20 00:00:00.000 010
2 2008-05-08 00:00:00.000 003
3 2008-05-01 00:00:00.000 001(所影响的行数为 3 行)*/
-- Author:Flystone
-- Version:V1.001 Date:2008-05-15 初稿
-- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常
-- 2、 增加了形如yyyy-mm-dd hh:mm:ss
-- yyyy-m-d h:m:s 格式的处理
*/-- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(日期 smalldatetime,单号 int)
Go
Insert into ta
select '2008-05-01',001 union all
select '2008-05-20',010 union all
select '2008-05-08',003
Go
--Start
select identity(int,1,1) as id,* into #temp1 from (select top 100 percent * from ta order by 日期 desc ) a select * from #temp1
drop table #temp1
--Result:
/*id 日期 单号
----------- ------------------------------------------------------ -----------
1 2008-05-20 00:00:00 10
2 2008-05-08 00:00:00 3
3 2008-05-01 00:00:00 1(所影响的行数为 3 行)
*/
--End
没有乱啊
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (dt varchar(10),code varchar(3))
insert into #t select '2008-05-01','001'
union all select '2008-05-20','010'
union all select '2008-05-08','003'
select identity(int,1,1) as ID ,dt,code into # from #t
order by dt descselect * from #drop table #/*
ID dt code
1 2008-05-20 010
2 2008-05-08 003
3 2008-05-01 001
*/