Add a column to table(Handling)

原始数据
姓名 代码 年月
A 空 201001
B 空 201002
B 空 201002
C 空 201002
C 空 201002
A DM 201003
B DM 201003

查询结果
姓名 批次 代码 年月
A 1 空 201001
B 1 空 201002
B 2 空 201002
C 1 空 201002
C 2 空 201002
A 1 DM 201003
B 2 DM 201003
也就是说要满足以下条件
1，当代码这一列为空的时候，同一年月同一个人，批次加1
2，当代码不为空时，同一年月批次加1（姓名不用管）

solusion1:

--creator:xman_78tom

declare @tb table ([姓名] varchar(20),[代码] varchar(20),[年月] char(6));
insert into @tb
select 'A',null,'201001' union all
select 'B',null,'201002' union all
select 'B',null,'201002' union all
select 'C',null,'201002' union all
select 'C',null,'201002' union all
select 'A','DM','201003' union all
select 'B','DM','201003';

select [姓名],
(case when [代码] is null
then row_number() over (partition by [年月],[姓名] order by [姓名])
else row_number() over (partition by [年月] order by [姓名]) end) [批次],
[代码], [年月]
from @tb;

 

solusion 2:

--creator:denghui_li

--思路:给表加个行号
--对于每一行　当代码 is null求出行号<=该行号且同代码且同姓名且同年月的记录条数
--当代码不为空时　求出行号<=该行号且同代码且同年月的记录条数（原句：2，当代码不为空时，同一年月批次加1（姓名不用管））
use tempdb;
go
create table dbo.tb
(
  name varchar(5) not null,
  dm   varchar(5) null,
  ny   datetime
);
insert into dbo.tb
select 'A', null, '201001' union all
select 'B', null, '201002' union all
select 'B', null, '201002' union all
select 'C', null, '201002' union all
select 'C', null, '201002' union all
select 'A', 'DM', '201003' union all
select 'B', 'DM', '201003';

with cte as
(
  select name,dm,ny,
         row_number()over(order by (select 1))as row
  from dbo.tb
)
select name,dm,ny,
       (select COUNT(name)
        from cte
        where ny=ct1.ny
         and row<=ct1.row
         and ISNULL(dm,1)=ISNULL(ct1.dm,1)
         and (case when ct1.dm is null then ct1.name else ct1.dm end)
             =
             (case when ct1.dm is null then name else dm end))as pc
from cte as ct1;