我需要在映射表中为每个单元创建一个新的行号。请检查以下示例数据和预期结果。
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
它工作正常，但我觉得我为此做了太多的编码。那么有没有更好的方法来实现这一目标？
这是我的尝试。您可能需要根据实际架构进行一些更改。
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
它可能没有你想的那么简单。无论如何都要发布它。
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;
似乎你想要：
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;
也就是说，新行号为：
linenumber表中的行号 -- 表示每个代码的首次出现。- 基于不匹配的行号加上以
linenumber为单位的计数的序列号。
我会使用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;