mysql - 使用查询的结果进行进一步的计算，这些结果在同一查询中给出错误

Question

我已经这样写了查询 我想用这些结果进一步计算单个查询中的所有结果

SELECT  TIMESTAMPDIFF(MONTH,memberToMship_StartDate,memberToMship_EndDate)* memberToMship_ChargePerPeriod As totalcontractamountperiod ,
   DATEDIFF(GREATEST(NOW(),memberToMship_DueDay),memberToMship_StartDate)/30 AS exppayments,
   30* memberToMship_ChargePerPeriod / DATEDIFF(memberToMship_EndDate,memberToMship_StartDate) AS monthlyamount, 
   (totalcontractamountperiod/monthlyamount) as exppayments, 
   member_Id 
FROM membertomships;

是否有可能所有内容都使用 mysql 进行单个查询

但它给出了这样的错误

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

任何人都可以知道这个

修改代码:我已经通过使用子查询完成了这样的操作....

SELECT  
  EXPPAYMENT WHERE EXPPAYMENT =(TIMESTAMPDIFF(MONTH, memberToMship_StartDate,memberToMship_EndDate)* memberToMship_ChargePerPeriod As totalcontractamountperiod ,
  DATEDIFF(GREATEST(NOW(),memberToMship_DueDay),memberToMship_StartDate)/30 AS exppayments , 
  30* memberToMship_ChargePerPeriod / DATEDIFF(memberToMship_EndDate,memberToMship_StartDate) AS monthlyamount , 
  (totalcontractamountperiod/monthlyamount) as exppayments, member_Id 
FROM membertomships);

但它给出了这样的错误

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE EXPPAYMENT =(TIMESTAMPDIFF(MONTH, memberToMship_StartDate,memberToMship_En' at line 1

这是修改后的查询，并向查询添加了两个函数，但仍然出现错误

SELECT 
  Total,
  (datdiff-1) as diff,
  ceil(ExpPayments-Total) AS datdiff,                 
  ExpPayments, 
  MonthlyAmount, 
  (Total/MonthlyAmount) as ExpPayments2, 
  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;

这样的错误

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

请帮忙

               SELECT 
                SourceTable.Total,
               ceil(SourceTable.ExpPayments-SourceTable.Total) AS datdiff,                 
              ceil(SourceTable.ExpPayments-SourceTable.Total) -1 as diff,
                       ADDDATE(ADDDATE(NOW(), INTERVAL FLOOR(ceil(SourceTable.ExpPayments-   SourceTable.Total) -1) MONTH), INTERVAL DAY(NOW()) - SourceTable.memberToMship_DueDay DAY) As expdate,
              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

错误代码:1054“字段列表”中存在未知列“SourceTable.memberToMship_DueDay”

Answer 1

我会缩写一些列名，因为它们有点长，这样查询就可以更清晰:

SELECT  TIMESTAMPDIFF(MONTH,StartDate,EndDate)* ChargePerPeriod As Total,
        DATEDIFF(GREATEST(NOW(),DueDay),StartDate)/30 AS ExpPayments,    
        30* ChargePerPeriod / DATEDIFF(EndDate,StartDate) AS MonthlyAmount,
--->    (Total/MonthlyAmount) as ExpPayments2,     
        member_Id  
FROM membertomships; 

问题出在所指出的行中，因为您在定义它们的同一列列表中使用了Total 和MonthlyAmount。我知道它看起来应该可以工作，但是这样使用 SQL 是非法的。

你可以做的是:

1. 重复定义值的条件(并希望数据库只计算一次)，例如

这个:

SELECT  TIMESTAMPDIFF(MONTH,StartDate,EndDate)* ChargePerPeriod As Total,
        DATEDIFF(GREATEST(NOW(),DueDay),StartDate)/30 AS ExpPayments,    
        30* ChargePerPeriod / DATEDIFF(EndDate,StartDate) AS MonthlyAmount,
        (TIMESTAMPDIFF(MONTH,StartDate,EndDate)* ChargePerPeriod / (30* ChargePerPeriod / DATEDIFF(EndDate,StartDate))) as ExpPayments2,     
        member_Id  
FROM membertomships; 

1. 像子查询一样重新设计表达式。您可以一步完成计算，然后使用这些结果进行进一步的计算，例如

这个:

SELECT Total, ExpPayments, MonthlyAmount, (Total/MonthlyAmount) as ExpPayments2, member_Id  from
(
  SELECT  TIMESTAMPDIFF(MONTH,StartDate,EndDate)* ChargePerPeriod As Total,
          DATEDIFF(GREATEST(NOW(),DueDay),StartDate)/30 AS ExpPayments,    
          30* ChargePerPeriod / DATEDIFF(EndDate,StartDate) AS MonthlyAmount,
          member_Id  
  FROM membertomships
) as SourceTable

顺便说一句，原始查询中的两列名为 ExpPayments，我将第二列重命名为 ExpPayments2。