标题是否符合网友的问题宗旨,另外讨论,暂且如此。想了妥解问题,还得看原讨论题。
这是一个网上的问题如下,
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
;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
下面是Insus.NET实现方法。另创建一张临时表,比网友的数据表添加一个字段ID,删除一些与问题无关的字段。
在MS SQL Server 2017版本中实现。
Insus.NET的方法是使用ROW_NUMBER和PARTITION时行分组:
先分析一列[sno],看看:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
;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;
另一列[rno]:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
;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;
以上加个ID列,主要是为了让大家看到它的排序,拿到的是第一列非空的值。网友的问题,直接按[repair_no]排序即可。
下面代码是把上面2列合并在一起。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
;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;
使用色彩来引示可以看到明白:
把以上方法去解决网友的问题,却得到另外一个结果:
对比一下,原来空值也应该有,就是当一个值都没有时,才用空值填充。
看来得改写一下程序,创建临时表,存储结果。
2个字段分别处理,把结果MERGE来合并至临时表中:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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