mysql - 为什么我的查询不起作用

Question

我接到一份工作，要连接多个表中的数据。它的一部分是在包含“联合”之前完成的。现在我必须从“migrated_loan_account”表中获取数据。所以我加入了它。但是当我添加字段时它不起作用。当我评论这 4 个字段时，它起作用了。我不明白该怎么做。谁能帮我解决这个问题？!下面是我的代码:

` SELECT -- office_info.office_ref_code AS old_office_code
                        -- , office_info.office_code
                        -- , office_info.office_name
                        -- , project_info.project_ref_code AS old_project_code
                        -- , project_info.project_code
                        -- , project_info.project_name
                        -- ,
                        IFNULL(group_info.group_reference_number, '') AS old_group_code
                        , IFNULL(RIGHT(group_info.group_code, 5), '') AS group_code
                        , IFNULL(group_info.group_name, '') AS group_name
                        , IFNULL(member_info.reference_no, '') AS old_member_code
                        , RIGHT(member_info.member_no, 5) AS member_code
                        , member_info.member_name
                        , IFNULL(savings_account.savings_balance,0) AS savings_balance
                        , la.account_ref_no AS old_loan_no
                        , la.account_no AS loan_no
                        , loan_status.name AS loan_status
                        , DATE(la.disbursement_date) AS disbursement_date
                        , IFNULL(la.disbursed_amount,0) AS disbursed_amount
                        , IFNULL(la.outstanding_balance,0) AS loan_due
                        , IFNULL(la.principal_outstanding,0) AS principal_outstanding
                        , IFNULL(la.interest_outstanding,0) AS interest_outstanding
                        , IFNULL(la.interest_realizable,0) AS interest_realizable
                        , IFNULL(la.overdue_amount,0) AS overdue_amount
                        , IFNULL(migrated_loan_account.outstanding_balance, 0) AS radar_loan_due
                        , IFNULL(migrated_loan_account.principal_outstanding, 0) AS radar_principal_outstanding
                        , IFNULL(migrated_loan_account.interest_realizable, 0) AS radar_interest_realizable
                        , IFNULL(migrated_loan_account.overdue_amount, 0) AS radar_overdue_amount


                  FROM member_info
                  INNER JOIN office_info ON
                  (office_info.id = member_info.branch_info_id)
                  INNER JOIN country_head_office ON
                  (country_head_office.id = office_info.country_head_office_id)
                  INNER JOIN country ON
                  (country.id = country_head_office.office_country_id)
                  INNER JOIN project_info ON
                  (project_info.id = member_info.project_info_id)
                  INNER JOIN country_program_info ON
                  (country_program_info.id = project_info.program_info_id)
                  INNER JOIN program_info ON
                  (program_info.id = country_program_info.program_info_id)
                  LEFT JOIN group_info ON
                  (group_info.id = member_info.group_info_id)
                  LEFT JOIN savings_account ON
                  (savings_account.member_info_id= member_info.id)
                  LEFT JOIN loan_account AS la ON
                  (la.member_id = member_info.id)
                  INNER JOIN
                  (
                      SELECT member_id, MAX(disbursement_date) AS max_date
                      FROM loan_account
                      GROUP BY member_id
                  ) mla ON la.member_id = mla.member_id AND la.disbursement_date = mla.max_date
                  LEFT JOIN loan_status ON
                  (loan_status.id = la.loan_status_id)
                  LEFT JOIN migrated_loan_account ON
                  (migrated_loan_account.loan_account_id = la.id)
                   WHERE   country.id = 1   AND 1=1  AND  project_info.id = 'BI0000000000000000000001'  AND  office_info.id = 'BI0000000000000000000363'  

                  UNION

                  SELECT -- office_info.office_ref_code AS old_office_code
                        -- , office_info.office_code
                        -- , office_info.office_name
                        -- , project_info.project_ref_code AS old_project_code
                        -- , project_info.project_code
                        -- , project_info.project_name
                        -- ,
                        IFNULL(group_info.group_reference_number, '') AS old_group_code
                        , IFNULL(RIGHT(group_info.group_code, 5), '') AS group_code
                        , IFNULL(group_info.group_name, '') AS group_name
                        , IFNULL(member_info.reference_no, '') AS old_member_code
                        , RIGHT(member_info.member_no, 5) AS member_code
                        , member_info.member_name
                        , IFNULL(savings_account.savings_balance,0) AS savings_balance
                        , 'NO LOAN' AS old_loan_no
                        , '' AS loan_no
                        , '' AS loan_status
                        , NULL AS disbursement_date
                        , 0 AS disbursed_amount
                        , 0 AS loan_due
                        , 0 AS principal_outstanding
                        , 0 AS interest_outstanding
                        , 0 AS interest_realizable
                        , 0 AS overdue_amount
                      FROM member_info
                      INNER JOIN office_info ON
                      (office_info.id = member_info.branch_info_id)
                      INNER JOIN country_head_office ON
                      (country_head_office.id = office_info.country_head_office_id)
                      INNER JOIN country ON
                      (country.id = country_head_office.office_country_id)
                      INNER JOIN project_info ON
                      (project_info.id = member_info.project_info_id)
                      INNER JOIN country_program_info ON
                      (country_program_info.id = project_info.program_info_id)
                      INNER JOIN program_info ON
                      (program_info.id = country_program_info.program_info_id)
                      LEFT JOIN group_info ON
                      (group_info.id = member_info.group_info_id)
                      LEFT JOIN savings_account ON
                      (savings_account.member_info_id= member_info.id)
                       WHERE   country.id = 1   AND 1=1  AND  project_info.id = 'BI0000000000000000000001'  AND  office_info.id = 'BI0000000000000000000363'   AND member_info.id NOT IN (SELECT loan_account.member_id FROM loan_account)
                  ORDER BY -- old_office_code, old_project_code,
                      old_group_code, old_member_code;
              `

当我评论以下字段时，它起作用了。请帮忙快...

/*, ifnull(migrated_loan_account.outstanding_balance, 0) as radar_loan_due
                    , ifnull(migrated_loan_account.principal_outstanding, 0) as radar_principal_outstanding
                    , ifnull(migrated_loan_account.interest_realizable, 0) as radar_interest_realizable
                    , ifnull(migrated_loan_account.overdue_amount, 0) as radar_overdue_amount*/

Answer 1

如果您将这些字段注释掉，它会起作用吗？在取消注释这些字段的情况下，您是否将它们包含在 UNION 中？联合必须返回相同数量的列。

将以下内容添加到 UNION 的第二个查询中:

, 0 AS radar_loan_due
, 0 AS radar_principal_outstanding
, 0 AS radar_interest_realizable
, 0 AS radar_overdue_amount