执行前子查询基本错误 [英] Subqueries basic error before execute

问题描述

我不知道这个查询有什么问题，多次尝试后我无法运行.请指出这有什么问题我该如何解决.谢谢.

I don't whats issue with this query and after multiple times trying I'm unable to run. Kindly point out whats wrong with this how can I fix that. Thanks.

   SELECT 
     cd.dr
    FROM
    (
     (SELECT SUM(cc.credit_amount) AS cr FROM cust_credit cc) cc
      UNION  
     (SELECT  SUM(cd.debit_amount) AS dr FROM cust_debit cd ) cd  
     )

错误是.

#1064 - 您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以获取在第 7 行的 'cd ) LIMIT 0, 25' 附近使用的正确语法

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'cd ) LIMIT 0, 25' at line 7

推荐答案

您不能为 UNION 中的子查询提供别名，也不能使用别名来引用子查询的结果.而UNION中的列别名总是来自第一个子查询中的别名，所以你不能引用dr.

You can't give aliases to subqueries in a UNION, and you can't refer to results from the subqueries using an alias. And the column aliases in a UNION always come from the aliases in the first subquery, so you can't refer to dr.

你可以做的是:

SELECT amount
FROM (
    SELECT 'cc' AS type, SUM(credit_amount) as amount FROM cust_credit
    UNION ALL
    SELECT 'cd' AS type, SUM(debit_amount) AS amount FROM cust_debit
) x
WHERE type = 'cd'

或者不使用 UNION，您可以将查询放在 SELECT 列表中.

Or instead of using UNION, you can put the queries in the SELECT list.

SELECT dr AS amount
FROM (
    SELECT (SELECT SUM(credit_amount) FROM cust_credit) AS cr,
           (SELECT SUM(debit_amount) FROM cust_debit) AS dr
) x

这篇关于执行前子查询基本错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！