列值中获取第一个非空的值
分类:
IT文章
•
2024-09-17 18:17:55
标题是否符合网友的问题宗旨，另外讨论，暂且如此。想了妥解问题，还得看原讨论题。
这是一个网上的问题如下，
![]()
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,null sno,'467769309410' rno union all
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno
)select * from temp
--以上原始数据
--以下想要的结果
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,'467769309410' rno union all
--select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno
)select * from temp
Source Code
下面是Insus.NET实现方法。另创建一张临时表，比网友的数据表添加一个字段ID，删除一些与问题无关的字段。
在MS SQL Server 2017版本中实现。
Insus.NET的方法是使用ROW_NUMBER和PARTITION时行分组：
先分析一列[sno]，看看：
![]()
;WITH s AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [repair_no] ORDER BY [id],[sno]) AS [ROW_NUM],
[id],
[repair_no],
[sno]
FROM #T
WHERE [sno] IS NOT NULL
)
SELECT [ROW_NUM],[id],[repair_no],[sno] FROM s;
Source Code
另一列[rno]:
![]()
;WITH
r AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [repair_no] ORDER BY [id],[rno]) AS [ROW_NUM],
[id],
[repair_no],
[rno]
FROM #T
WHERE [rno] IS NOT NULL
)
SELECT [ROW_NUM],[id],[repair_no],[rno] FROM r;
Source Code
以上加个ID列，主要是为了让大家看到它的排序，拿到的是第一列非空的值。网友的问题，直接按[repair_no]排序即可。
下面代码是把上面2列合并在一起。
![]()
;WITH s AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [repair_no] ORDER BY [id],[sno]) AS [ROW_NUM],
[id],
[repair_no],
[sno]
FROM #T
WHERE [sno] IS NOT NULL
),
r AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [repair_no] ORDER BY [id],[rno]) AS [ROW_NUM],
[id],
[repair_no],
[rno]
FROM #T
WHERE [rno] IS NOT NULL
)
SELECT s.[repair_no],[sno],[rno] FROM s
INNER JOIN r on (s.[repair_no] = r.[repair_no])
WHERE s.[ROW_NUM] = 1 AND r.ROW_NUM = 1;
Source Code
![]()
CREATE TABLE #ok_result([repair_no] INT,[sno] nvarchar(50),[rno] NVARCHAR(50))
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,null sno,'467769309410' rno union all
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno
),s AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [repair_no] ORDER BY repair_no) AS [ROW_NUM],
[repair_no],
[sno]
FROM temp
WHERE [sno] IS NOT NULL
)
MERGE #ok_result AS Target
USING (SELECT [repair_no],[sno] FROM s WHERE [ROW_NUM] = 1) AS Source
ON (Target.[repair_no] = Source.[repair_no])
WHEN MATCHED THEN
UPDATE SET target.[sno] = source.[sno]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([repair_no],[sno]) VALUES ([repair_no],[sno]);
;with temp as
(
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,null sno,'467769309410' rno union all
select '63738893' repair_no,'20190504' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno union all
select '63738793' repair_no,'20190508' report_date,'HES2418819040700003'service_sheet_no,'467769309411' sno,null rno
),r AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY [repair_no] ORDER BY repair_no) AS [ROW_NUM],
[repair_no],
[rno]
FROM temp
WHERE [rno] IS NOT NULL
)
MERGE #ok_result AS Target
USING (SELECT [repair_no],[rno] FROM r WHERE [ROW_NUM] = 1) AS Source
ON (Target.[repair_no] = Source.[repair_no])
WHEN MATCHED THEN
UPDATE SET target.[rno] = source.[rno]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([repair_no],[sno]) VALUES ([repair_no],[rno]);
SELECT [repair_no],[sno],[rno] FROM #ok_result
