Update A Set FBNO = (Case When FBTime Is Null Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3) Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End) From FixBug ASelect * From FixBug
--如果FBTime為Null的也要賦值 Update A Set FBNO = (Case When FBTime Is Null Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3) Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End), FBTime = IsNull(FBTime, GetDate()) From FixBug A
--創建測試環境 Create Table FixBug (FBid Int, FBNO Char(8), FBTime DateTime, FBDesc Nvarchar(20)) --插入數據 Insert FixBug Select 1, '2005_001', '2005-03-23 13:41', N'故障' Union All Select 2, '2005_002', '2005-03-25 02:01', N'故障' Union All Select 3, '2006_001', '2006-06-03 17:51', N'故障' Union All Select 4, '2007_001', '2007-01-04 10:11', N'缺陷' Union All Select 5, '2007_002', '2007-02-03 15:33', N'缺陷' Union All Select 6, '2007_003', '2007-03-16 09:31', N'缺陷' Union All Select 7, '2007_004', '2007-03-17 07:26', N'故障' Union All Select 8, '2007_005', '2007-05-07 12:22', N'缺陷' Union All Select 9, '2007_006', '2007-06-03 13:21', N'缺陷' Union All Select 10, NULL, NULL, N'故障' Union All Select 11, NULL, '2006-02-03 15:26', N'故障' Union All Select 12, '2006_003', '2006-04-03 17:27', N'故障' GO --測試 --如果FBTime為Null的也要賦值 Update A Set FBNO = (Case When FBTime Is Null Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3) Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End), FBTime = IsNull(FBTime, GetDate()) From FixBug ASelect * From FixBug GO --刪除測試環境 Drop Table FixBug --結果 /* FBid FBNO FBTime FBDesc 1 2005_001 2005-03-23 13:41:00.000 故障 2 2005_002 2005-03-25 02:01:00.000 故障 3 2006_003 2006-06-03 17:51:00.000 故障 4 2007_001 2007-01-04 10:11:00.000 缺陷 5 2007_002 2007-02-03 15:33:00.000 缺陷 6 2007_003 2007-03-16 09:31:00.000 缺陷 7 2007_004 2007-03-17 07:26:00.000 故障 8 2007_005 2007-05-07 12:22:00.000 缺陷 9 2007_006 2007-06-03 13:21:00.000 缺陷 10 2007_007 2007-08-24 09:55:11.440 故障 11 2006_001 2006-02-03 15:26:00.000 故障 12 2006_002 2006-04-03 17:27:00.000 故障 */
語句中,我多加入了一種判斷, 就是同時有多個FBTime為NULL的情況。
insert into FixBug select 1, '2005_001', '2005-03-23 13:41', '故障' union all select 2, '2005_002', '2005-03-25 02:01', '故障' union all select 3, '2006_001', '2006-06-03 17:51', '故障' union all select 4, '2007_001', '2007-01-04 10:11', '缺陷' union all select 5, '2007_002', '2007-02-03 15:33', '缺陷' union all select 6, '2007_003', '2007-03-16 09:31', '缺陷' union all select 7, '2007_004', '2007-03-17 07:26', '故障' union all select 8, '2007_005', '2007-05-07 12:22', '缺陷' union all select 9, '2007_006', '2007-06-03 13:21', '缺陷'insert into FixBug select 10,null,null,'' union all select 11,null,null,'' union all select 12,null,'2006-06-01 17:51','' union all select 13,null,'2007-01-01 17:51',''
--創建測試環境 Create Table FixBug (FBid Int, FBNO Char(8), FBTime DateTime, FBDesc Nvarchar(20)) --插入數據 insert into FixBug select 1, '2005_001', '2005-03-23 13:41', '故障' union all select 2, '2005_002', '2005-03-25 02:01', '故障' union all select 3, '2006_001', '2006-06-03 17:51', '故障' union all select 4, '2007_001', '2007-01-04 10:11', '缺陷' union all select 5, '2007_002', '2007-02-03 15:33', '缺陷' union all select 6, '2007_003', '2007-03-16 09:31', '缺陷' union all select 7, '2007_004', '2007-03-17 07:26', '故障' union all select 8, '2007_005', '2007-05-07 12:22', '缺陷' union all select 9, '2007_006', '2007-06-03 13:21', '缺陷'insert into FixBug select 10,null,null,'' union all select 11,null,null,'' union all select 12,null,'2006-06-01 17:51','' union all select 13,null,'2007-01-01 17:51','' GO --測試 --如果FBTime為Null的也要賦值 Update A Set FBNO = (Case When FBTime Is Null Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3) Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End), FBTime = IsNull(FBTime, GetDate()) From FixBug ASelect * From FixBug GO --刪除測試環境 Drop Table FixBug --結果 /* FBid FBNO FBTime FBDesc 1 2005_001 2005-03-23 13:41:00.000 故障 2 2005_002 2005-03-25 02:01:00.000 故障 3 2006_002 2006-06-03 17:51:00.000 故障 4 2007_002 2007-01-04 10:11:00.000 缺陷 5 2007_003 2007-02-03 15:33:00.000 缺陷 6 2007_004 2007-03-16 09:31:00.000 缺陷 7 2007_005 2007-03-17 07:26:00.000 故障 8 2007_006 2007-05-07 12:22:00.000 缺陷 9 2007_007 2007-06-03 13:21:00.000 缺陷 10 2007_008 2007-08-24 10:16:10.363 11 2007_009 2007-08-24 10:16:10.363 12 2006_001 2006-06-01 17:51:00.000 13 2007_001 2007-01-01 17:51:00.000 */
1 2005_001 2005-03-23 13:41 故障
2 2005_002 2005-03-25 02:01 故障
3 2006_001 2006-06-03 17:51 故障
4 2007_001 2007-01-04 10:11 缺陷
5 2007_002 2007-02-03 15:33 缺陷
6 2007_003 2007-03-16 09:31 缺陷
7 2007_004 2007-03-17 07:26 故障
8 2007_005 2007-05-07 12:22 缺陷
9 2007_006 2007-06-03 13:21 缺陷
10 NULL NULL 故障
11 NULL 2006-02-03 15:26 故障
11 2006_003 2006-04-03 17:27 故障比如如上表,那么我更新的数据应该这样
FBid FBNO FBTime FBDesc
1 2005_001 2005-03-23 13:41 故障
2 2005_002 2005-03-25 02:01 故障
3 2006_003 2006-06-03 17:51 故障
4 2007_001 2007-01-04 10:11 缺陷
5 2007_002 2007-02-03 15:33 缺陷
6 2007_003 2007-03-16 09:31 缺陷
7 2007_004 2007-03-17 07:26 故障
8 2007_005 2007-05-07 12:22 缺陷
9 2007_006 2007-06-03 13:21 缺陷
10 2007_007 2007-08-24 09:36 故障
11 2006_001 2006-02-03 15:26 故障
11 2006_002 2006-04-03 17:27 故障我比较笨,汗
只能用这种方法来解析
Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3)
Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End)
From FixBug ASelect * From FixBug
Update A Set FBNO = (Case When FBTime Is Null
Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3)
Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End),
FBTime = IsNull(FBTime, GetDate())
From FixBug A
Create Table FixBug
(FBid Int,
FBNO Char(8),
FBTime DateTime,
FBDesc Nvarchar(20))
--插入數據
Insert FixBug Select 1, '2005_001', '2005-03-23 13:41', N'故障'
Union All Select 2, '2005_002', '2005-03-25 02:01', N'故障'
Union All Select 3, '2006_001', '2006-06-03 17:51', N'故障'
Union All Select 4, '2007_001', '2007-01-04 10:11', N'缺陷'
Union All Select 5, '2007_002', '2007-02-03 15:33', N'缺陷'
Union All Select 6, '2007_003', '2007-03-16 09:31', N'缺陷'
Union All Select 7, '2007_004', '2007-03-17 07:26', N'故障'
Union All Select 8, '2007_005', '2007-05-07 12:22', N'缺陷'
Union All Select 9, '2007_006', '2007-06-03 13:21', N'缺陷'
Union All Select 10, NULL, NULL, N'故障'
Union All Select 11, NULL, '2006-02-03 15:26', N'故障'
Union All Select 12, '2006_003', '2006-04-03 17:27', N'故障'
GO
--測試
--如果FBTime為Null的也要賦值
Update A Set FBNO = (Case When FBTime Is Null
Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3)
Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End),
FBTime = IsNull(FBTime, GetDate())
From FixBug ASelect * From FixBug
GO
--刪除測試環境
Drop Table FixBug
--結果
/*
FBid FBNO FBTime FBDesc
1 2005_001 2005-03-23 13:41:00.000 故障
2 2005_002 2005-03-25 02:01:00.000 故障
3 2006_003 2006-06-03 17:51:00.000 故障
4 2007_001 2007-01-04 10:11:00.000 缺陷
5 2007_002 2007-02-03 15:33:00.000 缺陷
6 2007_003 2007-03-16 09:31:00.000 缺陷
7 2007_004 2007-03-17 07:26:00.000 故障
8 2007_005 2007-05-07 12:22:00.000 缺陷
9 2007_006 2007-06-03 13:21:00.000 缺陷
10 2007_007 2007-08-24 09:55:11.440 故障
11 2006_001 2006-02-03 15:26:00.000 故障
12 2006_002 2006-04-03 17:27:00.000 故障
*/
union all select 2, '2005_002', '2005-03-25 02:01', '故障'
union all select 3, '2006_001', '2006-06-03 17:51', '故障'
union all select 4, '2007_001', '2007-01-04 10:11', '缺陷'
union all select 5, '2007_002', '2007-02-03 15:33', '缺陷'
union all select 6, '2007_003', '2007-03-16 09:31', '缺陷'
union all select 7, '2007_004', '2007-03-17 07:26', '故障'
union all select 8, '2007_005', '2007-05-07 12:22', '缺陷'
union all select 9, '2007_006', '2007-06-03 13:21', '缺陷'insert into FixBug select 10,null,null,''
union all select 11,null,null,''
union all select 12,null,'2006-06-01 17:51',''
union all select 13,null,'2007-01-01 17:51',''
Create Table FixBug
(FBid Int,
FBNO Char(8),
FBTime DateTime,
FBDesc Nvarchar(20))
--插入數據
insert into FixBug select 1, '2005_001', '2005-03-23 13:41', '故障'
union all select 2, '2005_002', '2005-03-25 02:01', '故障'
union all select 3, '2006_001', '2006-06-03 17:51', '故障'
union all select 4, '2007_001', '2007-01-04 10:11', '缺陷'
union all select 5, '2007_002', '2007-02-03 15:33', '缺陷'
union all select 6, '2007_003', '2007-03-16 09:31', '缺陷'
union all select 7, '2007_004', '2007-03-17 07:26', '故障'
union all select 8, '2007_005', '2007-05-07 12:22', '缺陷'
union all select 9, '2007_006', '2007-06-03 13:21', '缺陷'insert into FixBug select 10,null,null,''
union all select 11,null,null,''
union all select 12,null,'2006-06-01 17:51',''
union all select 13,null,'2007-01-01 17:51',''
GO
--測試
--如果FBTime為Null的也要賦值
Update A Set FBNO = (Case When FBTime Is Null
Then Cast(Year(GetDate()) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where FBTime Is Null And FBid <= A.FBid) + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(GetDate())), 3)
Else Cast(Year(FBTime) As Varchar) + '_' + Right(1000 + (Select Count(FBid) From FixBug Where Year(FBTime) = Year(A.FBTime) And FBTime <= A.FBTime), 3) End),
FBTime = IsNull(FBTime, GetDate())
From FixBug ASelect * From FixBug
GO
--刪除測試環境
Drop Table FixBug
--結果
/*
FBid FBNO FBTime FBDesc
1 2005_001 2005-03-23 13:41:00.000 故障
2 2005_002 2005-03-25 02:01:00.000 故障
3 2006_002 2006-06-03 17:51:00.000 故障
4 2007_002 2007-01-04 10:11:00.000 缺陷
5 2007_003 2007-02-03 15:33:00.000 缺陷
6 2007_004 2007-03-16 09:31:00.000 缺陷
7 2007_005 2007-03-17 07:26:00.000 故障
8 2007_006 2007-05-07 12:22:00.000 缺陷
9 2007_007 2007-06-03 13:21:00.000 缺陷
10 2007_008 2007-08-24 10:16:10.363
11 2007_009 2007-08-24 10:16:10.363
12 2006_001 2006-06-01 17:51:00.000
13 2007_001 2007-01-01 17:51:00.000 */