主页 / dba / 问题 / 58841
Accepted
BuZZ-dEE
Asked: 2014-02-14 00:34:53 +0800 CST

如何在 SQL 查询中重写 IFNULL 或 COALESCE？

  • 772

我有一种方法可以重写 SQL 查询，所以我可以避免IFNULLor COALESCE，因为 HXTT DBF JDBC 驱动程序（支持 SQL92）会抛出以下错误：

Caused by: java.sql.SQLException: The aggregate function SUM(NETTO) can only be used in select-list, HAVING clause, and ORDER BY clause of SELECT statement.

以下查询导致上述错误：

SELECT
(   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
    FROM tckopf AS shipManagementInvoice
    WHERE shipManagementInvoice.referenzid = 1
        AND shipManagementInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
    FROM tckopf AS shipManagementCreditNote
    WHERE shipManagementCreditNote.referenzid = 1
        AND shipManagementCreditNote.btyp = 6 )
 AS shipManagementResult

如果我删除IFNULLor COALESCE，则没有错误。

jdbc foxpro

3 个回答

  1. Best Answer

    有几种方法可以重写查询，甚至更多，因为两个子查询都使用相同的基表。不知道为什么会抛出错误以及应该归咎于谁，JDBC驱动程序，Foxpro或其他东西，所以这里有一些替代方案：

    • (1) 对两个搜索使用一个查询：

      SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto
                         WHEN t.btyp = 6 THEN - t.netto 
                                         ELSE 0 
                    END), 0) AS shipManagementResult
FROM tckopf AS t
WHERE t.referenzid = 1 ;

    • (2) 在外部级别中拉取COALESCE()orIFNULL()函数调用，或者使用原始查询：

      SELECT 
  COALESCE( ( SELECT SUM(i.netto) 
              FROM tckopf AS i
              WHERE i.referenzid = 1
                AND i.btyp = 5 
            ), 0)
- COALESCE( ( SELECT SUM(c.netto) 
              FROM tckopf AS c
              WHERE c.referenzid = 1
                AND c.btyp = 6 
            ), 0)
  AS shipManagementResult ;

    • (2b) 或上述 (1st) 查询：

      SELECT COALESCE( 
  ( SELECT SUM(CASE WHEN t.btyp = 5 THEN t.netto
                    WHEN t.btyp = 6 THEN - t.netto 
                                    ELSE 0 
                END)
    FROM tckopf AS t
    WHERE t.referenzid = 1
  ), 0) AS shipManagementResult ;
    • 2

  2. 这也有效。：

    SELECT SUM ( CASE WHEN btyp = 6 THEN netto ELSE 0 END) - SUM(CASE WHEN btyp = 5 THEN netto ELSE 0 END)
FROM tckopf
WHERE referenzid = 1;
    • 0

  3. 尝试ISNULL(SUM (shipManagementInvoice.netto ), 0)

    • -1

相关问题