mysql - 在主查询中使用子查询

Question

我已经完成了像这样的子查询但是它给出了错误

                    SELECT 
              sourceTable3.Total,
              (sourcetable3.datediff-1) as diff,
            ADDDATE(ADDDATE(NOW(), INTERVAL FLOOR(diff) MONTH), INTERVAL DAY(NOW()) -            memberToMship_DueDay DAY) As expdate,
              ExpPayments, 
            MonthlyAmount, 
            SourceTable3.ExpPayments2, 
             FROM
             (
             SELECT 
                SourceTable2.Total,
             (SourceTable2.datdiff-1) as diff,
             SourceTable2.datdiff,                 
            ExpPayments, 
            MonthlyAmount, 
            SourceTable2.ExpPayments2, 
           member_Id 
          FROM 
          (
             SELECT 
              SourceTable.Total,
               ceil(SourceTable.ExpPayments-SourceTable.Total) AS datdiff,                 
              SourceTable.ExpPayments, 
                SourceTable.MonthlyAmount, 
            (SourceTable.Total/SourceTable.MonthlyAmount) as ExpPayments2, 
               SourceTable.member_Id 
          FROM 
           ( 
              SELECT                          TIMESTAMPDIFF(MONTH,memberToMship_StartDate,memberToMship_EndDate)*      memberToMship_ChargePerPeriod As Total,     
DATEDIFF(GREATEST(NOW(),memberToMship_DueDay),memberToMship_StartDate)/30 AS ExpPayments,
30* memberToMship_ChargePerPeriod / DATEDIFF(memberToMship_EndDate,memberToMship_StartDate) AS MonthlyAmount, 
member_Id
            FROM 
            membertomships 
         ) as SourceTable
      ) as SourceTable2
  ) as sourceTable3

但是它给出了这样的错误...

                         Error Code: 1054
                         Unknown column 'sourcetable3.datediff' in 'field list'

谁能帮忙...

修改后的代码:

修改了'e'得到了这样的错误

错误代码:1054“字段列表”中的未知列“memberTo_Mship_DueDay”

修改后的代码:

这是我的表结构

                                membership table
                              memberToMship_Id
                             memberToMship_StartDate
                             memberToMship_EndDate
                             memberToMship_DueDay
                             member_Id

                       I want finally these columns expdate , diff , ExpPayments, 
            MonthlyAmount, 





            MODIFIED CODE :




                SELECT 
           SourceTable3.Total,

          ADDDATE(ADDDATE(NOW(), INTERVAL FLOOR(diff) MONTH), INTERVAL DAY(NOW()) -          memberToMship_DueDay DAY) As expdate,
          (Sourcetable3.datdiff-1) as diff,
         ExpPayments, 
            MonthlyAmount, 
          SourceTable3.ExpPayments2, 
           SourceTable3.member_Id
          FROM
          (
              SELECT 
            SourceTable2.Total,
             (SourceTable2.datdiff-1) as diff,
            SourceTable2.datdiff,                 
            ExpPayments, 
            MonthlyAmount, 
           SourceTable2.ExpPayments2, 
            member_Id 
                FROM 
                (
                        SELECT 
               SourceTable.Total,
                 ceil(SourceTable.ExpPayments-SourceTable.Total) AS datdiff,                 
                SourceTable.ExpPayments, 
               SourceTable.MonthlyAmount, 
              (SourceTable.Total/SourceTable.MonthlyAmount) as ExpPayments2, 
               SourceTable.member_Id 

               FROM 
               ( 
              SELECT TIMESTAMPDIFF(MONTH,memberToMship_StartDate,memberToMship_EndDate)*      memberToMship_ChargePerPeriod As Total,     
                   DATEDIFF(GREATEST(NOW(),memberToMship_DueDay),memberToMship_StartDate)/30 AS ExpPayments,
           30* memberToMship_ChargePerPeriod /                      DATEDIFF(memberToMship_EndDate,memberToMship_StartDate) AS MonthlyAmount, 
member_Id
           FROM 
           membertomships 
          ) as SourceTable
        ) as SourceTable2
      ) as SourceTable3

和错误

                        Error Code: 1054
                       Unknown column 'memberToMship_DueDay' in 'field list'

谁能帮忙...

Answer 1

我认为您需要从 datediff 中删除 e，这样它就变成了 sourcetable3.datdiff。