mysql - 如何对所有标题进行求和，即使其他标题为空

Question

我的查询目标是对 (3) 个表中的所有字段求和，但如果其他一些标题清空了 final_total_sum，那么我在生成 final_total_sum 输出时会遇到一些问题为空....但如果所有 titles 都不为空，我的查询会生成 final_total_sum(output)。

我想做的是，即使其他标题为空，我的查询仍然可以生成final_total_sum(output)。

http://s38.photobucket.com/user/eloginko/media/output_zpsfcab9d54.png.html

当前查询:

SELECT *,
    ROUND(interview_sum +
          other_sum +
          edu_attain2_sum +
          experience2_sum +
          trainings2_sum +
          eligibility2_sum) AS final_total_sum
FROM (
    SELECT
        ROUND((SELECT SUM(t2.inttotal)
         FROM app_interview2 AS t2 
         WHERE t2.atic = t.atic)/7,1)
         AS interview_sum,

        ROUND((SELECT SUM(o2.ototal)
         FROM other_app2 AS o2 
         WHERE o2.oaic = t.atic)/7,1)
         AS other_sum,

        ROUND((SELECT SUM(s1.edu_attain2)
         FROM qual_stan2 AS s1 
         WHERE s1.oaic2 = t.atic)/7,1)
         AS edu_attain2_sum,

        ROUND((SELECT SUM(s2.experience2)
         FROM qual_stan2 AS s2 
         WHERE s2.oaic2 = t.atic)/7,1)
         AS experience2_sum,

        ROUND((SELECT SUM(s3.trainings2)
         FROM qual_stan2 AS s3 
         WHERE s3.oaic2 = t.atic)/7,1)
         AS trainings2_sum,

        ROUND((SELECT SUM(s4.eligibility2)
         FROM qual_stan2 AS s4 
         WHERE s4.oaic2 = t.atic)/7,1)
         AS eligibility2_sum,

        t.atid,
        t.atic,
        t.atname,
        t.region,
        t.town,
        t.uniq_id,
        t.position,
        t.salary_grade,
        t.salary

    FROM app_interview2 AS t
    WHERE uniq_id = '$q'
    GROUP BY t.atname 
    HAVING COUNT(DISTINCT t.atic)) subq

Answer 1

尝试:

   ROUND( ifnull(interview_sum,0) +
          ifnull(other_sum,0) +
          ifnull(edu_attain2_sum,0) +
          ifnull(experience2_sum,0) +
          ifnull(trainings2_sum,0) +
          ifnull(eligibility2_sum,0)) AS final_total_sum

在 SQL 中，x + NULL 始终给出 NULL，ifnull 函数将 null 转换为 0