如何调整DB2 SQL语句，实现含短编号或空字段时仅显示单行

需求：调整SQL语句，使每个主编号（如41031052-1）仅显示一行——若存在对应的短编号（dashonly）则显示带该值的行，若无则显示dashonly为空的行，避免同一主编号重复出现。

原SQL语句 #

Select Distinct TRIM(TRANSLATE(itnbr,' ','F')),
Case When t3.dashonly Is NULL Then '' Else t3.dashonly End As dashonly
From amflib1.itmrva t1
Join webprddt1.drawmext17 t2 On t2.afctdwg = t1.uu25
Left Join webprddt1.wqmssoadn t3 On t3.itemno = t1.itnbr
Where t2.recseq = '0060' Order By 1

当前查询结果 #

00001           DASHONLY
 --------------- ---------------
 41031052-1      
 41031052-1      -1             
 41031052-10     
 41031052-11     -11            
 41031052-11     
 41031052-12     
 41031052-12     -12            
 41031052-13     
 41031052-14     
 41031052-15     
 41031052-17

期望结果 #

00001           DASHONLY
 --------------- ---------------
 41031052-1      -1             
 41031052-10     
 41031052-11     -11            
 41031052-12     -12            
 41031052-13     
 41031052-14     
 41031052-15     
 41031052-17

解决方案 #

通过分组主编号字段，对dashonly取最大值（非空值优先级高于空字符串），实现每个主编号仅保留一行有效数据：

SELECT 
    TRIM(TRANSLATE(itnbr,' ','F')) AS item_number,
    MAX(CASE WHEN t3.dashonly IS NULL THEN '' ELSE t3.dashonly END) AS dashonly
FROM amflib1.itmrva t1
JOIN webprddt1.drawmext17 t2 ON t2.afctdwg = t1.uu25
LEFT JOIN webprddt1.wqmssoadn t3 ON t3.itemno = t1.itnbr
WHERE t2.recseq = '0060'
GROUP BY TRIM(TRANSLATE(itnbr,' ','F'))
ORDER BY 1

说明 #

• 用GROUP BY替代DISTINCT，确保每个主编号唯一分组
• MAX()函数会自动选取该分组下非空的dashonly值（若存在），没有则保留空字符串，正好匹配需求

内容的提问来源于stack exchange，提问作者Steve Dyke