请帮写一条SQL语句，能把我的数据修正过来【在线等待】

Question

1.本来业务是这样的：案件分配的时候，根据案件的ID就会把之前分配过的给闭合，并且闭合日期和闭合人等于这次分配日期和分配人。
2.但是我之前分配的时候忘记闭合了,导致有些数据都没闭合,大哥帮我写一条SQL语句把下面的数据修正过来,
3.每一个案件ID只能有一条未闭合的数据，求各位大神帮我修一条SQL语句可以修正我下面的数据。创建表的代码
     create table #t_case_assign_log( ID int identity, 案件ID int, 分配日期 datetime, 分配人ID int, 闭合日期 datetime, 闭合人ID int,是否闭合 bit ) 
     go
     insert into #t_case_assign_log 
     select 8,'2010-4-2',10,'1900-1-1',0,0 union all
     select 8,'2010-5-2',12,'1900-1-1',0,0 union all
     select 8,'2010-7-12',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',12,'1900-1-1',0,0 union all
     select 10,'2010-4-2',10,'2010-5-4',0,0 union all
     select 8,'2010-8-2',10,'2010-8-4',14,1 union all
     select 8,'2010-8-4',14,'2010-9-1',12,1 union all
     select 9,'2010-8-4',10,'2010-8-9',8,1 union all
     select 10,'2010-8-5',10,'2010-9-4',8,1 union all
     select 8,'2010-9-1',12,'1900-1-1',0,0 union all
     select 9,'2010-8-9',8,'1900-1-1',0,0 union all
     select 10,'2010-9-4',8,'1900-1-1',0,0 
     go
     select *from #t_case_assign_log 我现在的数据

Answer 1

     create table #t_case_assign_log( ID int identity, 案件ID int, 分配日期 datetime, 分配人ID int, 闭合日期 datetime, 闭合人ID int,是否闭合 bit ) 
     go
     insert into #t_case_assign_log 
     select 8,'2010-4-2',10,'1900-1-1',0,0 union all
     select 8,'2010-5-2',12,'1900-1-1',0,0 union all
     select 8,'2010-7-12',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',12,'1900-1-1',0,0 union all
     select 10,'2010-4-2',10,'2010-5-4',0,0 union all
     select 8,'2010-8-2',10,'2010-8-4',14,1 union all
     select 8,'2010-8-4',14,'2010-9-1',12,1 union all
     select 9,'2010-8-4',10,'2010-8-9',8,1 union all
     select 10,'2010-8-5',10,'2010-9-4',8,1 union all
     select 8,'2010-9-1',12,'1900-1-1',0,0 union all
     select 9,'2010-8-9',8,'1900-1-1',0,0 union all
     select 10,'2010-9-4',8,'1900-1-1',0,0 
     go  with cte as
 (
     select ROW_NUMBER()over(partition by 案件ID order by 案件ID)num,*from #t_case_assign_log 
 )
 update cte set 闭合日期=分配日期,闭合人ID=分配人ID,是否闭合='1'
 where num<>1  select * from #t_case_assign_log
----------------------------------------------------------------------
ID          案件ID        分配日期                    分配人ID       闭合日期                    闭合人ID       是否闭合
----------- ----------- ----------------------- ----------- ----------------------- ----------- -----
1           8           2010-04-02 00:00:00.000 10          1900-01-01 00:00:00.000 0           0
2           8           2010-05-02 00:00:00.000 12          2010-05-02 00:00:00.000 12          1
3           8           2010-07-12 00:00:00.000 10          2010-07-12 00:00:00.000 10          1
4           9           2010-04-02 00:00:00.000 10          2010-04-02 00:00:00.000 10          1
5           9           2010-04-02 00:00:00.000 12          2010-04-02 00:00:00.000 12          1
6           10          2010-04-02 00:00:00.000 10          2010-05-04 00:00:00.000 0           0
7           8           2010-08-02 00:00:00.000 10          2010-08-02 00:00:00.000 10          1
8           8           2010-08-04 00:00:00.000 14          2010-08-04 00:00:00.000 14          1
9           9           2010-08-04 00:00:00.000 10          2010-08-04 00:00:00.000 10          1
10          10          2010-08-05 00:00:00.000 10          2010-08-05 00:00:00.000 10          1
11          8           2010-09-01 00:00:00.000 12          2010-09-01 00:00:00.000 12          1
12          9           2010-08-09 00:00:00.000 8           1900-01-01 00:00:00.000 0           0
13          10          2010-09-04 00:00:00.000 8           2010-09-04 00:00:00.000 8           1(13 行受影响)

Answer 2

create table #t_case_assign_log
(ID int identity, 案件ID int, 分配日期 datetime, 分配人ID int, 闭合日期 datetime, 
 闭合人ID int,是否闭合 bit ) 
 
     insert into #t_case_assign_log 
     select 8,'2010-4-2',10,'1900-1-1',0,0 union all
     select 8,'2010-5-2',12,'1900-1-1',0,0 union all
     select 8,'2010-7-12',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',12,'1900-1-1',0,0 union all
     select 10,'2010-4-2',10,'2010-5-4',0,0 union all
     select 8,'2010-8-2',10,'2010-8-4',14,1 union all
     select 8,'2010-8-4',14,'2010-9-1',12,1 union all
     select 9,'2010-8-4',10,'2010-8-9',8,1 union all
     select 10,'2010-8-5',10,'2010-9-4',8,1 union all
     select 8,'2010-9-1',12,'1900-1-1',0,0 union all
     select 9,'2010-8-9',8,'1900-1-1',0,0 union all
     select 10,'2010-9-4',8,'1900-1-1',0,0 
with t as
(select *,
        row_number() over(partition by 案件ID order by ID desc) 'rn'
 from #t_case_assign_log)
update a
 set a.闭合日期=b.闭合日期,
     a.闭合人ID=b.闭合人ID,
     a.是否闭合=1
 from t a
 inner join (select * from t where rn=1) b on a.案件ID=b.案件ID
 where a.rn>1 and a.是否闭合=0
select * from #t_case_assign_log order by 案件ID,是否闭合/*
ID          案件ID        分配日期                    分配人ID       闭合日期                    闭合人ID       是否闭合
----------- ----------- ----------------------- ----------- ----------------------- ----------- -----
11          8           2010-09-01 00:00:00.000 12          1900-01-01 00:00:00.000 0           0
1           8           2010-04-02 00:00:00.000 10          1900-01-01 00:00:00.000 0           1
2           8           2010-05-02 00:00:00.000 12          1900-01-01 00:00:00.000 0           1
3           8           2010-07-12 00:00:00.000 10          1900-01-01 00:00:00.000 0           1
7           8           2010-08-02 00:00:00.000 10          2010-08-04 00:00:00.000 14          1
8           8           2010-08-04 00:00:00.000 14          2010-09-01 00:00:00.000 12          1
12          9           2010-08-09 00:00:00.000 8           1900-01-01 00:00:00.000 0           0
9           9           2010-08-04 00:00:00.000 10          2010-08-09 00:00:00.000 8           1
4           9           2010-04-02 00:00:00.000 10          1900-01-01 00:00:00.000 0           1
5           9           2010-04-02 00:00:00.000 12          1900-01-01 00:00:00.000 0           1
13          10          2010-09-04 00:00:00.000 8           1900-01-01 00:00:00.000 0           0
6           10          2010-04-02 00:00:00.000 10          1900-01-01 00:00:00.000 0           1
10          10          2010-08-05 00:00:00.000 10          2010-09-04 00:00:00.000 8           1(13 row(s) affected)
*/

Answer 3

create table #t_case_assign_log( ID int identity, 案件ID int, 分配日期 datetime, 分配人ID int, 闭合日期 datetime, 闭合人ID int,是否闭合 bit ) 
     go
     insert into #t_case_assign_log 
     select 8,'2010-4-2',10,'1900-1-1',0,0 union all
     select 8,'2010-5-2',12,'1900-1-1',0,0 union all
     select 8,'2010-7-12',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',12,'1900-1-1',0,0 union all
     select 10,'2010-4-2',10,'2010-5-4',0,0 union all
     select 8,'2010-8-2',10,'2010-8-4',14,1 union all
     select 8,'2010-8-4',14,'2010-9-1',12,1 union all
     select 9,'2010-8-4',10,'2010-8-9',8,1 union all
     select 10,'2010-8-5',10,'2010-9-4',8,1 union all
     select 8,'2010-9-1',12,'1900-1-1',0,0 union all
     select 9,'2010-8-9',8,'1900-1-1',0,0 union all
     select 10,'2010-9-4',8,'1900-1-1',0,0 
     go
     
;with cte as
(
select *,rn=ROW_NUMBER() over(partition by 案件ID order by 分配日期) from #t_case_assign_log 
) 
select a.案件ID,a.分配日期,a.分配人ID,b.分配日期 as 闭合日期,b.分配人ID as 闭合人,
case when b.分配日期 is not null then 1 else 0 end as 是否闭合
from cte a
left join cte b on a.rn+1=b.rn and a.案件ID=b.案件ID/*
案件ID 分配日期 分配人ID 闭合日期 闭合人 是否闭合
8 2010-04-02 00:00:00.000 10 2010-05-02 00:00:00.000 12 1
8 2010-05-02 00:00:00.000 12 2010-07-12 00:00:00.000 10 1
8 2010-07-12 00:00:00.000 10 2010-08-02 00:00:00.000 10 1
8 2010-08-02 00:00:00.000 10 2010-08-04 00:00:00.000 14 1
8 2010-08-04 00:00:00.000 14 2010-09-01 00:00:00.000 12 1
8 2010-09-01 00:00:00.000 12 NULL NULL 0
9 2010-04-02 00:00:00.000 10 2010-04-02 00:00:00.000 12 1
9 2010-04-02 00:00:00.000 12 2010-08-04 00:00:00.000 10 1
9 2010-08-04 00:00:00.000 10 2010-08-09 00:00:00.000 8 1
9 2010-08-09 00:00:00.000 8 NULL NULL 0
10 2010-04-02 00:00:00.000 10 2010-08-05 00:00:00.000 10 1
10 2010-08-05 00:00:00.000 10 2010-09-04 00:00:00.000 8 1
10 2010-09-04 00:00:00.000 8 NULL NULL 0
*/

Answer 4

 ;with cte as
     (
     select ROW_NUMBER()over(partition by 案件ID order by 案件ID)num,*from #t_case_assign_log 
     )
     update cte set 闭合日期=分配日期,闭合人ID=分配人ID,是否闭合='1'
     where num<>1

Answer 5

能不能为 null的那些数据 为 1900-1-1 和0  还有请把ID显示出来

Answer 6

create table #t_case_assign_log( ID int identity, 案件ID int, 分配日期 datetime, 分配人ID int, 闭合日期 datetime, 闭合人ID int,是否闭合 bit ) 
     go
     insert into #t_case_assign_log 
     select 8,'2010-4-2',10,'1900-1-1',0,0 union all
     select 8,'2010-5-2',12,'1900-1-1',0,0 union all
     select 8,'2010-7-12',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',10,'1900-1-1',0,0 union all
     select 9,'2010-4-2',12,'1900-1-1',0,0 union all
     select 10,'2010-4-2',10,'2010-5-4',0,0 union all
     select 8,'2010-8-2',10,'2010-8-4',14,1 union all
     select 8,'2010-8-4',14,'2010-9-1',12,1 union all
     select 9,'2010-8-4',10,'2010-8-9',8,1 union all
     select 10,'2010-8-5',10,'2010-9-4',8,1 union all
     select 8,'2010-9-1',12,'1900-1-1',0,0 union all
     select 9,'2010-8-9',8,'1900-1-1',0,0 union all
     select 10,'2010-9-4',8,'1900-1-1',0,0 
     go
      
;with cte as
(
select *,rn=ROW_NUMBER() over(partition by 案件ID order by 分配日期) from #t_case_assign_log 
) 
select a.案件ID,a.分配日期,a.分配人ID,ISNULL(b.分配日期,'1900-01-01') as 闭合日期,ISNULL(b.分配人ID,0) as 闭合人,
    case when b.分配日期 is not null then 1 else 0 end as 是否闭合
from cte a
left join cte b on a.rn+1=b.rn and a.案件ID=b.案件ID

Answer 7

能不能为 null的那些数据 为 1900-1-1 和0  还有请把ID显示出来为什么我的数据库的数据没有修改？你是不是只是查询的？不是修改数据的？

Answer 8

明显是只查询不修改，修改还不容易吗？select * into #t from上面的代码，然后再关联update即可

Answer 9

写得真心好。关于Null，在关联Update时去掉Null的行就可以了。

Answer 10

第一步：;with cte as
(
select *,rn=ROW_NUMBER() over(partition by 案件ID order by 分配日期) from #t_case_assign_log 
) 
select a.案件ID,a.分配日期,a.分配人ID,b.分配日期 as 闭合日期,b.分配人ID as 闭合人,
    case when b.分配日期 is not null then 1 else 0 end as 是否闭合 into #t
from cte a
left join cte b on a.rn+1=b.rn and a.案件ID=b.案件ID第二步：
update #t_case_assign_log
set #t_case_assign_log.需要修改的列名=#t.对应列名 --我不知道你要更新哪些列，要更新的全部写上。
from #t_case_assign_log inner join #t on #t_case_assign_log.主键=#t.主键

Answer 11

谢谢你，我晚上回家就全部给你分，我想问问你，我一直不理解你那个 ";with cte as ()" 它为什么在其他语句外面，我网上查了一下，但是网上说得太复杂，你能给我一个通俗易懂的解释吗？大神？

Answer 12

那个叫做CTE，联机丛书有，而with cte只是随意的名字，你可以with abc as 都行，这个是2005出现的。貌似是标准语法，oracle那些都有，他的目的其实是生成一个临时的结果集，然后在后面用，你完全可以用一个临时表来替换;with cte as
(
select *,rn=ROW_NUMBER() over(partition by 案件ID order by 分配日期) from #t_case_assign_log 
) 这部分，比如select *,rn=ROW_NUMBER() over(partition by 案件ID order by 分配日期) into cte
from #t_case_assign_log 只是由于CTE执行完毕关了查询窗口就消失，不用手动删除，所以在测试的时候比较方便

Answer 13

谢谢你，我晚上回家就全部给你分，我想问问你，我一直不理解你那个 ";with cte as ()" 它为什么在其他语句外面，我网上查了一下，但是网上说得太复杂，你能给我一个通俗易懂的解释吗？大神？其实就是一个虚拟的表，你写个select语句，就产生了一个虚拟表，下面的语句，都能直接引用这个结果集，进行进一步的计算，说到底就是方便的作用哈。