为每个映射生成新的行号

Question

我需要在我的映射表中为每个单元创建一个新的行号。请查看以下示例数据和期望结果。

Lines table
+--------+------------+------+------+
| FileId | linenumber | code | unit |
+--------+------------+------+------+
|      1 |          1 | A    | NULL |
|      1 |          2 | B    | NULL |
|      1 |          3 | C    | NULL |
+--------+------------+------+------+

map table
+------+------+
| code | unit |
+------+------+
| A    | c1   |
| A    | c2   |
| B    | c3   |
| B    | c4   |
| B    | c5   |
+------+------+

expected result
+--------+------------+------+------+
| FileId | Linenumber | code | unit |
+--------+------------+------+------+
|      1 |          1 | A    | c1   |
|      1 |          2 | B    | c3   |
|      1 |          4 | A    | c2   |
|      1 |          5 | B    | c4   |
|      1 |          6 | B    | c5   |
+--------+------------+------+------+

代码 A 有两个单元 (c1 和 c2)，单元 c1 将在第 1 行更新，c2 单元应在行表中可用的最后一行号之后插入为新行。同样的过程应该适用于所有代码。

我目前的方法是

if object_id('tempdb..#lines') is not null drop table #lines
if object_id('tempdb..#map') is not null drop table #map
if object_id('tempdb..#Files') is not null drop table #Files
if object_id('tempdb..#Maptemp') is not null drop table #Maptemp

create table #lines(FileId int, linenumber int, code varchar(10), unit varchar(10))
create table #map(code varchar(10), unit varchar(10))
insert into #lines values (1,1,'A',null), (1,2,'B',null),(1, 3,'C',null)
insert into #map values ('A','c1'),('A','c2'),('B','c3'),('B','c4'),('B','c5')

select FileId, MaxLinenum = max(linenumber) into #Files
    from #lines
    group by FileId

select row_number()over(partition by code order by (select null)) Rn,* into #Maptemp
    from #map

select l.FileId,l.Linenumber,l.code, m.unit
    from #lines l 
    inner join #Files f on l.FileId = f.FileId
    inner join #Maptemp m on m.code = l.code 
    where m.rn = 1
union all
select l.FileId, f.MaxLinenum +row_number()over(partition by f.FileId order by (select null)),l.code, m.unit
    from #lines l 
    inner join #Files f on l.FileId = f.FileId
    inner join #Maptemp m on m.code = l.code 
    where m.rn > 1

这个方案可以正常工作，但我觉得我写了太多的代码。有没有更好的方法来实现这个功能？

- Pரதீப்

---

你的原始Lines表 - 是否保证每个code只有一行数据？ - Vladimir Baranov

@VladimirBaranov - 是的。 - Pரதீப்

Answer 1

这是我的尝试..根据您的实际架构可能需要一些更改。

DECLARE @MAXLINE INT = (SELECT MAX(linenumber) FROM #lines)

SELECT L.FileId
    ,CASE WHEN M.SNO = 1 THEN L.linenumber
        ELSE 
            @MAXLINE + ROW_NUMBER() OVER (PARTITION BY CASE WHEN M.SNO<>1
                THEN 1 END ORDER BY M.CODE ,M.UNIT)
        END LINE_NUMBER
, M.code
, M.unit
FROM #lines L
INNER JOIN
(
    SELECT ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY(UNIT)) SNO,*
    FROM #map
)M ON L.code = M.code

结果：

FileId  LINE_NUMBER code    unit
1       1           A       c1
1       2           B       c3
1       4           A       c2
1       5           B       c4
1       6           B       c5

- Shakeer Mirza

---

谢谢，看起来比我的好。唯一的不满意之处是 @MAXLINE，可能会有多个 FileId，但这可以很容易地解决。 - Pரதீப்

Answer 2

这可能并不像您希望的那样简单。无论如何，还是要发布。

DECLARE @MaxLine INT;

SELECT @MaxLine = MAX(LineNumber)
FROM   lines;


IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    DROP TABLE #temp;

SELECT   l.fileId ,
         l.linenumber ,
         l.code ,
         MIN(m.unit) AS unit
INTO     #temp
FROM     #Lines l
         JOIN #map m ON l.code = m.code
GROUP BY l.fileId ,
         l.linenumber ,
         l.code;

SELECT *
FROM   #temp
UNION
SELECT l.fileId ,
       @MaxLine + ROW_NUMBER() OVER ( PARTITION BY l.fileId
                                      ORDER BY l.code ) ,
       l.code ,
       m.unit
FROM   #LINES l
       JOIN #map m ON l.code = m.code
       LEFT JOIN #temp t ON l.code = t.code
                            AND m.unit = t.unit
WHERE  t.code IS NULL;

- AB_87

Answer 3

看起来你想要：

select m.*,
       coalesce(l.linenumber,
                c.cnt + row_number() over (partition by l.linenumber order by m.linenumber)
               ) as new_linenumber
from (select m.*,
             row_number() over (partition by code order by linenumber) as seqnum
      from #map m
     ) m left join
     #lines l
     on l.code = m.code and m.seqnum = 1 cross join
     (select count(*) as cnt from #lines) c;

也就是说，新的行号可以是以下两种情况之一：

1. 在linenumber表中第一次出现代码的行号。
2. 基于未匹配的行号加上linenumber中的计数而产生的顺序号。

- Gordon Linoff

Answer 4

我会使用first_value()函数：

with t as (
     select l.fileid, l.code, m.unit, (case when f_value = m.unit then l.linenumber end) as lines, cnt.tcnt 
     from #lines l inner join
          (select *, first_value(unit) over (partition by code order by unit) as f_value
           from #map
          ) m
          on m.code = l.code cross join
         (select count(*) tcnt from #lines) cnt
)

select fileid, code, unit, 
       coalesce(lines, 
                tcnt + sum(case when lines is null then 1 end) over (order by unit)
               ) as linenumber
from t;

- Yogesh Sharma

---

很不幸，我们仍然被困在 Sql Server 2008 中!! - Pரதீப்