合并来自表 sql server 中多行的记录 [英] Merging records from multiple rows in table sql server

问题描述

我在 t_resourcetable 中有一些脏的资源使用记录，看起来像这样

<前>resNo subres startdate enddate1 2 2012-01-02 22:03:00.000 2012-01-03 00:00:00.0001 2 2012-01-03 00:00:00.000 2012-01-04 00:00:00.0001 2 2012-01-04 00:00:00.000 2012-01-04 16:23:00.0001 3 2012-01-06 16:23:00.000 2012-01-06 22:23:00.0002 2 2012-01-04 05:23:00.000 2012-01-06 16:23:00.000

我需要以这种方式合并那些脏行

<前>resNo subres startdate enddate1 2 2012-01-02 22:03:00.000 2012-01-04 16:23:00.0001 3 2012-01-06 16:23:00.000 2012-01-06 22:23:00.0002 2 2012-01-04 05:23:00.000 2012-01-06 16:23:00.000

这应该更新到同一张表.我有超过 40k 行，所以不能使用游标.请帮助我通过更优化的 sql 语句来清理它.

提供的解决方案没有遇到类似的场景

<前>resNo subres startdate enddate1 2 2012-01-02 22:03:00.000 2012-01-03 00:00:00.0001 2 2012-01-03 00:00:00.000 2012-01-04 00:00:00.0001 2 2012-01-04 00:00:00.000 2012-01-04 16:23:00.0001 2 2012-01-14 10:09:00.000 2012-01-15 00:00:00.0001 2 2012-01-15 00:00:00.000 2012-01-16 00:00:00.0001 2 2012-01-16 00:00:00.000 2012-01-16 03:00:00.0001 3 2012-01-06 16:23:00.000 2012-01-06 22:23:00.0002 2 2012-01-04 05:23:00.000 2012-01-06 16:23:00.000

我需要以这种方式合并那些脏行

<前>resNo subres startdate enddate1 2 2012-01-02 22:03:00.000 2012-01-04 16:23:00.0001 2 2012-01-14 10:09:00.000 2012-01-16 03:00:00.0001 3 2012-01-06 16:23:00.000 2012-01-06 22:23:00.0002 2 2012-01-04 05:23:00.000 2012-01-06 16:23:00.000

请帮助我解决这个脏数据问题.

解决方案

对于 SQL Server 2005，您可以执行以下操作:

创建表#temp(resNo int,subres int,结束日期时间，主键 (resNo, subres))-- 将 enddate 所需的值存储在临时表中插入#temp选择 resNo,子资源，max(enddate) 作为结束日期来自 t_resourcetable按 resNo, subres 分组-- 删除重复项，保留最小开始日期的行删除T从 (select row_number() over(partition by resNo, subres order by startdate) as rn来自 t_resourcetable) 作为 T其中 rn >1-- 根据需要设置结束日期更新 T 设置结束日期 = tmp.enddate从 t_resourcetable 作为 T内连接 #temp 作为 tmp在 T.resNo = tmp.resNo 和t.subres = tmp.subres其中 T.enddate <>tmp.enddate删除表#temp

I have some dirty resource usage records in t_resourcetable which looks like this

resNo   subres    startdate                        enddate
1        2        2012-01-02 22:03:00.000          2012-01-03 00:00:00.000
1        2        2012-01-03 00:00:00.000          2012-01-04 00:00:00.000
1        2        2012-01-04 00:00:00.000          2012-01-04 16:23:00.000
1        3        2012-01-06 16:23:00.000          2012-01-06 22:23:00.000
2        2        2012-01-04 05:23:00.000          2012-01-06 16:23:00.000

I need those dirty rows to be merged in such way

resNo   subres    startdate                        enddate
1        2        2012-01-02 22:03:00.000          2012-01-04 16:23:00.000
1        3        2012-01-06 16:23:00.000          2012-01-06 22:23:00.000
2        2        2012-01-04 05:23:00.000          2012-01-06 16:23:00.000

This should get updated to the same table. I have more than 40k rows so cannot use a cursor. Please help me clean up this through more optimized sql statements.

Solution provided does not encounter the scenario like

resNo   subres    startdate                        enddate
1        2        2012-01-02 22:03:00.000          2012-01-03 00:00:00.000
1        2        2012-01-03 00:00:00.000          2012-01-04 00:00:00.000
1        2        2012-01-04 00:00:00.000          2012-01-04 16:23:00.000
1        2        2012-01-14 10:09:00.000          2012-01-15 00:00:00.000
1        2        2012-01-15 00:00:00.000          2012-01-16 00:00:00.000
1        2        2012-01-16 00:00:00.000          2012-01-16 03:00:00.000
1        3        2012-01-06 16:23:00.000          2012-01-06 22:23:00.000
2        2        2012-01-04 05:23:00.000          2012-01-06 16:23:00.000

I need those dirty rows to be merged in such way

resNo   subres    startdate                        enddate
1        2        2012-01-02 22:03:00.000          2012-01-04 16:23:00.000
1        2        2012-01-14 10:09:00.000          2012-01-16 03:00:00.000
1        3        2012-01-06 16:23:00.000          2012-01-06 22:23:00.000
2        2        2012-01-04 05:23:00.000          2012-01-06 16:23:00.000

Please assist me with this dirty data problem.

解决方案

For SQL Server 2005 you could do something like this:

create table #temp
(
  resNo int,
  subres int,
  enddate datetime,
  primary key (resNo, subres)
)

-- Store the values you need for enddate in a temp table
insert into #temp
select resNo, 
       subres,
       max(enddate) as enddate
from t_resourcetable
group by resNo, subres

-- Delete duplicates keeping the row with min startdate
delete T
from (
        select row_number() over(partition by resNo, subres order by startdate) as rn
        from t_resourcetable
     ) as T
where rn > 1

-- Set enddate where needed
update T set enddate = tmp.enddate
from t_resourcetable as T
  inner join #temp as tmp
    on T.resNo = tmp.resNo and
       t.subres = tmp.subres
where T.enddate <> tmp.enddate

drop table #temp

这篇关于合并来自表 sql server 中多行的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！