DB2中如何将NULL替换为整数值？SQL计算可用语言数返回NULL求助

首先咱们拆解下问题的核心：你当前的嵌套子查询只有当存在匹配的bokid时才会返回数值，如果某本书没有对应的Translation数据（也就是子查询里没有结果），整个子查询就会直接返回NULL——这时候你在子查询内部用COALESCE或IFNULL根本起不到作用，因为子查询本身都没有输出了。

方案一：把整个子查询包裹在COALESCE里 #

你需要把统计语言数的嵌套子查询整体作为参数传给COALESCE，这样当子查询返回NULL时，就会用预设的1（对应原始语言本身）来替代。子查询里的COUNT(Språk)如果没有匹配行的话会返回0，加1后刚好是1，完全符合你的需求。

修改后的SQL语句如下：

SELECT DISTINCT 
    Title AS "Titel", 
    OriginalLanguage AS "Orginalspråk", 
    Genre AS "Genre", 
    (SELECT COUNT(Id) FROM Edition WHERE Edition.Book = Book.Id) AS "Antal upplagor", 
    COALESCE(
        (SELECT COUNT(Språk)+1 
         FROM (
             SELECT Book.Id AS bokid, Språk 
             FROM Edition, 
                  XMLTABLE('$TRANSLATIONS//Translation/@Language' COLUMNS Språk VARCHAR(20) PATH '.'), 
                  Book 
             WHERE Edition.Book = Book.Id 
             GROUP BY Språk, Book.Id
         ) sub 
         WHERE bokid = Book.Id 
         GROUP BY bokid
    ), 1) AS "Antal tillgängliga språk", 
    (SELECT COUNT(Author) FROM Authorship WHERE Authorship.Book = Book.Id) AS "Antal författare", 
    (SELECT MIN(Year) FROM Edition WHERE Edition.Book = Book.Id) AS "År första upplaga" 
FROM Book

方案二：用LEFT JOIN替代嵌套子查询（更简洁易维护） #

嵌套子查询虽然能实现功能，但可读性和性能都不如JOIN写法。你可以预先统计每本书的译文语言数量，再用LEFT JOIN关联主表，最后用COALESCE处理NULL：

WITH TranslationCounts AS (
    SELECT 
        Book.Id AS bokid, 
        COUNT(DISTINCT Språk) AS translation_count
    FROM Book
    LEFT JOIN Edition ON Edition.Book = Book.Id
    LEFT JOIN XMLTABLE('$TRANSLATIONS//Translation/@Language' COLUMNS Språk VARCHAR(20) PATH '.') ON 1=1
    GROUP BY Book.Id
)
SELECT 
    DISTINCT Book.Title AS "Titel", 
    Book.OriginalLanguage AS "Orginalspråk", 
    Book.Genre AS "Genre", 
    (SELECT COUNT(Id) FROM Edition WHERE Edition.Book = Book.Id) AS "Antal upplagor", 
    COALESCE(tc.translation_count + 1, 1) AS "Antal tillgängliga språk", 
    (SELECT COUNT(Author) FROM Authorship WHERE Authorship.Book = Book.Id) AS "Antal författare", 
    (SELECT MIN(Year) FROM Edition WHERE Edition.Book = Book.Id) AS "År första upplaga" 
FROM Book
LEFT JOIN TranslationCounts tc ON tc.bokid = Book.Id

逻辑验证 #

• 当某本书有译文语言时：translation_count是译文的数量，加1后就是译文+原始语言的总数
• 当某本书没有任何译文语言时：translation_count为NULL，COALESCE会把它替换成1，刚好对应原始语言本身

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