----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-07 10:15:21
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([name] varchar(2),[psd] int,[test] sql_variant)
insert [huang]
select '王',332,null union all
select '李',331,null union all
select '赵',330,null
--------------开始查询--------------------------SELECT name,psd,LEFT('000'+CAST(ROW_NUMBER()OVER(ORDER BY GETUTCDATE()) AS VARCHAR(4)),4)test
from [huang]
----------------结果----------------------------
/*
name psd test
---- ----------- -------
王 332 0001
李 331 0002
赵 330 0003
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-07 10:15:21
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([name] varchar(2),[psd] int,[test] sql_variant)
insert [huang]
select '王',332,null union all
select '李',331,null union all
select '赵',330,null
--------------开始查询--------------------------SELECT name,psd,LEFT('000'+CAST(ROW_NUMBER()OVER(ORDER BY GETUTCDATE()) AS VARCHAR(4)),4)test
from [huang]
----------------结果----------------------------
/*
name psd test
---- ----------- -------
王 332 0001
李 331 0002
赵 330 0003
*/
SET test=LEFT('000'+CAST(ROW_NUMBER()OVER(ORDER BY GETUTCDATE()) AS VARCHAR(4)),4)
报错 'ROW_NUMBER' 不是可以识别的 函数名。
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-07 10:15:21
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([name] varchar(2),[psd] int,[test] sql_variant)
insert [huang]
select '王',332,null union all
select '李',331,null union all
select '赵',330,null
--------------开始查询--------------------------SELECT name,psd,IDENTITY(INT,1,1)id INTO #t
from [huang]UPDATE huang
SET huang.test=RIGHT('0000'+cast(#t.id AS varchar(4)),4)
FROM huang INNER JOIN #t ON huang.NAME=#t.NAME AND huang.psd=#t.psdSELECT * FROM huang
----------------结果----------------------------
/*
name psd test
---- ----------- -------
王 332 0001
李 331 0002
赵 330 0003
*/