sql - 为每个映射生成新的行号
问题描述
我需要在我的映射表中为每个单元创建一个新的行号。请检查以下示例数据和预期结果。
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
推荐阅读
- java - Cassandra有线协议中“持续时间”的ProtocolID?
- ceph - Ceph 恢复软件无法在最新版本上运行
- django - Django Select2Widget 没有用脆的形式正确地设置样式
- excel - 在第一行的值第一次匹配后删除所有行
- matlab - 以矩阵形式从matlab中的多级对象获取属性
- javascript - 禁用“离开站点?” 使用 ContentTools 时的问题
- python - 在 __init__ 中设置 self.var 时出现 Python 错误
- postgresql - PostgreSQL 中的快速随机行:为什么 time (floor(random()*N)) + (select * from a where id = const) 比 select where id = random 少 100 倍?
- c# - Unity Build Doast 的工作方式与编辑器相同
- spring - 使用 JPA 存储库,jpql,本机查询查询