mysql - 选择包含计算字段的查询，该计算字段使用具有多个条件的子查询

Question

三张表:

COST_SAVINGS:COST_SAVINGS_ID、ONE_TIME_CREDIT、CREATION_DATE、INVOICE_ID(FK 到发票表)

发票:INVOICE_ID、INVOICE_CURRENCY_CODE

EXCHANGE_RATE:CURRENCY_RATE，CURRENCY_DATE

我正在报告成本节省(第一个表)。挑战在于每个成本节省金额可以采用不同的货币，因此我需要一个字段来显示基于发票表中的货币以及 Exchange.Ex_Date 和 Cost_Savings.Create_Date 之间匹配的月份/年份的转换金额。

我收到一条错误消息:

single-row subquery returns more than one row

这是我到目前为止所拥有的:

SELECT 
COST_SAVINGS.COST_SAVINGS_ID, 
COST_SAVINGS.CLAIM_TYPE, 
COST_SAVINGS.COMMENTS, 
COST_SAVINGS.COST_SAVINGS_STATUS, 
COST_SAVINGS.CREATION_DATE, 
COST_SAVINGS.DESCRIPTION,
COST_SAVINGS.ONE_TIME_CREDIT AS CREDIT_IN_NATIVE_CURRENCY,
FINANCE_INVOICE.CURRENCY_CODE,
COST_SAVINGS.ONE_TIME_CREDIT * 
    (SELECT EXCHANGE_RATE.CURRENCY_RATE 
     FROM EXCHANGE_RATE 
     WHERE EXTRACT (MONTH FROM COST_SAVINGS.CREATION_DATE) = EXTRACT (MONTH FROM EXCHANGE_RATE.CURRENCY_DATE)
     AND EXTRACT (YEAR FROM COST_SAVINGS.CREATION_DATE) = EXTRACT (YEAR FROM EXCHANGE_RATE.CURRENCY_DATE)
     AND FINANCE_INVOICE.CURRENCY_CODE = EXCHANGE_RATE.CURRENCY_CODE) AS CREDIT_IN_USD

FROM COST_SAVINGS

LEFT JOIN FINANCE_INVOICE ON COST_SAVINGS.INVOICE_ID = FINANCE_INVOICE.INVOICE_ID

我觉得问题可能出在我的子查询中的第三个 WHERE 子句(尝试匹配货币代码)。我不确定如何解决它。有什么想法吗？

Answer 1

尝试将LIMIT 1放入子查询中:

...RENCY_CODE = EXCHANGE_RATE.CURRENCY_CODE LIMIT 1) AS CREDIT_IN_USD

我相信您的子查询返回多行。当您在 SELECT 子句中使用子查询代替列名时，这种方法不起作用。

如果您不想使用子查询代替列名，请尝试这样的操作。您将加入子查询生成的虚拟表。

这是汇率的虚拟表。它使用 GROUP BY 生成一行(或者每月/年/货币代码不生成行。如果任何月份/年份/代码的原始表中有多于一行，则它们的平均值为 AVG()。您还可以使用 MAX() 或 MIN()。

          SELECT AVG(CURRENCY_RATE) CURRENCY_RATE,
                 CURRENCY_CODE,
                 EXTRACT(MONTH FROM CURRENCY_DATE) MONTH,
                 EXTRACT(YEAR FROM CURRENCY_DATE) YEAR
            FROM EXCHANGE_RATE 
           GROUP BY CURRENCY_CODE, 
                 EXTRACT(MONTH FROM CURRENCY_DATE),
                 EXTRACT(YEAR FROM CURRENCY_DATE)

尝试一下并说服自己它有效。

然后将其构建到您的整体查询中。

SELECT 
      COST_SAVINGS.COST_SAVINGS_ID, 
      COST_SAVINGS.whatever,   ...   
      FINANCE_INVOICE.CURRENCY_CODE,
      (COST_SAVINGS.ONE_TIME_CREDIT * RATE.CURRENCY_RATE) AS CREDIT_IN_USD
FROM COST_SAVINGS
LEFT JOIN FINANCE_INVOICE ON COST_SAVINGS.INVOICE_ID = FINANCE_INVOICE.INVOICE_ID
LEFT JOIN (SELECT AVG(CURRENCY_RATE) CURRENCY_RATE,
                 CURRENCY_CODE,
                 EXTRACT(MONTH FROM CURRENCY_DATE) MONTH,
                 EXTRACT(YEAR FROM CURRENCY_DATE) YEAR
            FROM EXCHANGE_RATE 
           GROUP BY CURRENCY_CODE, 
                 EXTRACT(MONTH FROM CURRENCY_DATE),
                 EXTRACT(YEAR FROM CURRENCY_DATE)
    ) RATE ON RATE.CURRENCY_CODE = FINANCE_INVOICE.CURRENCY_CODE
          AND RATE.YEAR  = EXTRACT(YEAR  FROM COST_SAVINGS.CREATION_DATE)
          AND RATE.MONTH = EXTRACT(MONTH FROM COST_SAVINGS.CREATION_DATE)