自动编号sql问题
有一张表a 主要字段如下
FID(主键) FDate(插入时间)
1 2013-1-1 08:00:00
2 2013-1-1 08:30:00
3 2013-1-1 08:35:00
4 2013-1-2 09:00:00
现在增加了一个字段FNUM(编号)是按照插入时间来生成每天从1开始 +1累加 如下
FID(主键) FDate(插入时间) FNUM
1 2013-1-1 08:00:00 1
2 2013-1-1 08:30:00 2
7 2013-1-1 08:35:00 3
8 2013-1-2 09:00:00 1
新增记录只取当天最大的那个编号+1 比如我先删掉主键为1的记录,再插一条2013-1-1的记录 编号为4.
-----------------------------------------------
因为我是新加的字段 请问原来的数据怎么修改?新加记录的sql又怎么写?
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (FID int,FDate datetime)
insert into [TB]
select 1,'2013-1-1 08:00:00' union all
select 2,'2013-1-1 08:30:00' union all
select 3,'2013-1-1 08:35:00' union all
select 4,'2013-1-2 09:00:00'
select * from [TB]
SELECT *, ROW_NUMBER() over(PARTITION BY convert(VARCHAR(10),fdate,120) ORDER BY fdate) AS Fnum
FROM TB
/*
FIDFDateFnum
12013-01-01 08:00:00.0001
22013-01-01 08:30:00.0002
32013-01-01 08:35:00.0003
42013-01-02 09:00:00.0001*/
[解决办法]
IF OBJECT_ID('[Ta]') IS NOT NULL
DROP TABLE [Ta]
GO
CREATE TABLE [Ta] (FID int,FDate datetime)
insert into [Ta]
select 1,'2013-1-1 08:00:00' union
select 2,'2013-1-1 08:30:00' union
select 3,'2013-1-1 08:35:00' union
select 4,'2013-1-2 09:00:00'
Update a Set a.Number=b.Row_Num
From
Ta a
Inner Join
(
Select FID,Row_Num=Row_Number() OVER (PARTITION BY CONVERT(CHAR(10),FDate,120) Order By FDate)
From Ta
)b
On a.FID=b.FID;
[解决办法]