--查询
select category=lasteditby+convert(varchar(6),lastiedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastiedidt<=a.lastiedidt) as varchar)
from 表a a--更新
update 表a set category=a.lasteditby+convert(varchar(6),a.lastiedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastiedidt<=a.lastiedidt) as varchar)
from 表a a
select category=lasteditby+convert(varchar(6),lastiedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastiedidt<=a.lastiedidt) as varchar)
from 表a a--更新
update 表a set category=a.lasteditby+convert(varchar(6),a.lastiedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastiedidt<=a.lastiedidt) as varchar)
from 表a a
select (case when max(category) is null then '01'+convert(char(6),getdate(),112)+'001'
else left(max(category),8)+right(ltrim(rtrim(cast(('1000000'+(max(right(category,3))+1)) as char))),3) end)
from A
where substring(category,3,6)=convert(char(6),getdate(),112)
其他的你再改一下
create table 表A(name varchar(6),category varchar(20),lasteditby varchar(2),lastedidt datetime)
insert into 表A
select 'ID-001',null,'01','2003-12-05 09:13:17.217'
union all select 'ID-001',null,'01','2003-12-05 09:14:20.220'
union all select 'ID-001',null,'01','2003-12-05 09:10:01.217'
union all select 'ID-001',null,'01','2003-12-05 11:03:11.245'
union all select 'ID-001',null,'01','2003-12-05 11:07:08.045' go
--直接查询得到category
select name,category=lasteditby+convert(varchar(6),lastedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastedidt<=a.lastedidt) as varchar)
,lasteditby,lastedidt
from 表a a order by name,lasteditby,lastedidtgo
--更新表,生成category
update 表a set category=a.lasteditby+convert(varchar(6),a.lastedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastedidt<=a.lastedidt) as varchar)
from 表a a--显示更新结果
select * from 表A order by name,lasteditby,lastedidt
go
--删除测试环境
drop table 表A/*--测试结果--直接查询的结果
name category lasteditby lastedidt
------ -------------------- ---------- -------------------------
ID-001 012003121 01 2003-12-05 09:10:01.217
ID-001 012003122 01 2003-12-05 09:13:17.217
ID-001 012003123 01 2003-12-05 09:14:20.220
ID-001 012003124 01 2003-12-05 11:03:11.247
ID-001 012003125 01 2003-12-05 11:07:08.047(所影响的行数为 5 行)
--更新处理的结果name category lasteditby lastedidt
------ -------------------- ---------- -------------------------
ID-001 012003121 01 2003-12-05 09:10:01.217
ID-001 012003122 01 2003-12-05 09:13:17.217
ID-001 012003123 01 2003-12-05 09:14:20.220
ID-001 012003124 01 2003-12-05 11:03:11.247
ID-001 012003125 01 2003-12-05 11:07:08.047(所影响的行数为 5 行)
--*/
--创建表
create table 表A(name varchar(6),category varchar(20),lasteditby varchar(2),lastedidt datetime)
go
--创建触发器,自动生成category的值:
create trigger t_insert on 表A
for insert
as
update 表a set category=a.lasteditby+convert(varchar(6),a.lastedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastedidt<=a.lastedidt) as varchar)
from 表a a where isnull(category,'')=''
go--插入数据测试(注意,我插入数据的时候并没有给category赋值)
insert into 表A
select 'ID-001',null,'01','2003-12-05 09:13:17.217'
union all select 'ID-001',null,'01','2003-12-05 09:14:20.220'
union all select 'ID-001',null,'01','2003-12-05 09:10:01.217'
union all select 'ID-001',null,'01','2003-12-05 11:03:11.245'
union all select 'ID-002',null,'01','2003-12-05 11:07:08.045' --显示结果
select * from 表A order by name,lasteditby,lastedidtgo
--删除测试环境
drop table 表A/*--测试结果
name category lasteditby lastedidt
------ -------------------- ---------- -------------------------
ID-001 012003121 01 2003-12-05 09:10:01.217
ID-001 012003122 01 2003-12-05 09:13:17.217
ID-001 012003123 01 2003-12-05 09:14:20.220
ID-001 012003124 01 2003-12-05 11:03:11.247
ID-002 012003121 01 2003-12-05 11:07:08.047(所影响的行数为 5 行)
--*/
--创建一个触发器就行了,自动生成category的值:
create trigger t_insert on 表A
for insert
as
update 表a set category=a.lasteditby+convert(varchar(6),a.lastedidt,112)
+cast((select sum(1) from 表a where name=a.name and lastedidt<=a.lastedidt) as varchar)
from 表a a where isnull(category,'')=''
go
select (case when max(category) is null then ...不是在判断吗?
估计你没测试过,这条语句最好放在触发器里.