有一张表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又怎么写?
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又怎么写?
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/*
FID FDate Fnum
1 2013-01-01 08:00:00.000 1
2 2013-01-01 08:30:00.000 2
3 2013-01-01 08:35:00.000 3
4 2013-01-02 09:00:00.000 1*/
你这样会重复吧 比如现在就上面4条记录,我先删掉一条fid为1的数据,再插一条时间为2013-1-1的数据,你的fnum就等于3了 重复了
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;
比如现在就上面4条记录,我先删掉一条fid为1的数据,再插一条时间为2013-1-1的数据,你的fnum就等于3了 重复了
lz 需要的是按照日期的自增? 删除了也不能重复?这个lz可以自己建立一个序列表,日期、最大值 ,每次插入时,查询最大值+1 即可。
use TestDB;
go
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]
if object_id('tb2') is not null drop table [TB2]
goSELECT *, ROW_NUMBER() over(PARTITION BY convert(VARCHAR(10),fdate,120)
ORDER BY fdate) AS Fnum
into tb2
FROM TB
insert into tb2(fid,FDate,Fnum)
select 5,'2013-01-01 00:00:00',max(Fnum)+1
from tb2
where convert(date,FDate)='2013-01-01'select *
from tb2
酱紫?
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;
2、之後再寫入相同日期時,只需判斷一下相同日期FNum的最大值+1即可。看如下Declare @Dt1 DateTime
Set @Dt1='2013-01-01 14:01:01'
select @Dt1,FNum=Case When (Select Max(Number)+1 From Ta a Where Convert(Char(10),a.FDate,120)=Convert(Char(10),@Dt1,120) Group By Convert(Char(10),a.FDate,120)) IS NuLL Then 1 Else (Select Max(Number)+1 From Ta a Where Convert(Char(10),a.FDate,120)=Convert(Char(10),@Dt1,120) Group By Convert(Char(10),a.FDate,120)) End