Firebird

火鳥總欄

  • July 2, 2018

我有一個正在努力解決的問題。我遇到的問題是我希望它只顯示一列variancevalue作為結果。

SELECT
 DEPARTMENT.DESCRIPTION AS DepartmentName,
 SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
 SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
 sum(STOCKTAKELIST.QTYONHAND) Onhand,
 sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
 sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
 sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
 STOCKTAKELIST STOCKTAKELIST
 INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
 INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
 LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
 LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
 LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
 STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
 STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
 STOCKTAKELIST.IncludeInCount = 'Yes' AND
 STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
 STOCKTAKELIST.Status = 'Complete'
GROUP BY
 1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION

這可以做到嗎？如果可以，我該怎麼做？

如果您想用一個單列行替換目前查詢的整個輸出，其中VarianceValue包含原始輸出列的總數，那麼只需獲取目前查詢並刪除

  • 該GROUP BY條款，
  • 該ORDER BY條款，
  • 左連接，
  • SELECT以及除一個（最後一個）之外的所有術語：
SELECT
 ~~DEPARTMENT.DESCRIPTION AS DepartmentName,~~
 ~~SUBDEPARTMENT.DESCRIPTION AS SubDeptName,~~
 ~~SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,~~
 ~~sum(STOCKTAKELIST.QTYONHAND) Onhand,~~
 ~~sum(STOCKTAKELIST.STOCKCOUNT) StockCount,~~
 ~~sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,~~
 sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
 STOCKTAKELIST STOCKTAKELIST
 INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
 INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
 ~~LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE~~
 ~~LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE~~
 ~~LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE~~
WHERE
 STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
 STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
 STOCKTAKELIST.IncludeInCount = 'Yes' AND
 STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
 STOCKTAKELIST.Status = 'Complete'
~~GROUP BY~~
 ~~1,2,3~~
~~ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION~~

也就是說，如果沒有查詢中的部分，應該如下所示（只是為了使其絕對明確且易於複製粘貼）：

SELECT
 sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
 STOCKTAKELIST STOCKTAKELIST
 INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
 INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
WHERE
 STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
 STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
 STOCKTAKELIST.IncludeInCount = 'Yes' AND
 STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
 STOCKTAKELIST.Status = 'Complete';

內部連接也很有可能是不必要的。如果他們能影響結果，他們可能需要留下來。如果其中任何一個為真，它們會影響結果：

  • STOCKTAKELIST.INTRECNO_STOCK``STOCK.INTRECNO可以為空和/或可能包含在;中找不到的值
  • STOCK.INTRECNO_LOCATION可以為空和/或可能包含在LOCATION.INTRECNO.

如果您有外鍵關係，那麼重要的只是 and 的可空STOCKTAKELIST.INTRECNO_STOCK性STOCK.INTRECNO_LOCATION。現在，如果兩列都不可為空並且保證了引用完整性，那麼您應該能夠通過將查詢減少到以下方式來獲得相同的結果：

SELECT
 sum((STOCKCOUNT - QTYONHAND) * CostPrice) VarianceValue
FROM
 STOCKTAKELIST
WHERE
 ListCreate >= '01 Jun 2018' AND
 ListCreate <= '10 Jun 2018 23:59:59' AND
 IncludeInCount = 'Yes' AND
 STOCKCOUNT <> QTYONHAND AND
 Status = 'Complete';

這是你想要的？

SELECT
 DEPARTMENT.DESCRIPTION AS DepartmentName,
 SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
 SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
 sum(STOCKTAKELIST.QTYONHAND) Onhand,
 sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
 sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
 sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM STOCKTAKELIST STOCKTAKELIST
 INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
 INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
 LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
 LEFT JOIN SUBDEPARTMENT 
     ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE
    AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
 LEFT JOIN SUBSUBDEPARTMENT 
     ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE 
    AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE 
    AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
 STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
 STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
 STOCKTAKELIST.IncludeInCount = 'Yes' AND
 STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
 STOCKTAKELIST.Status = 'Complete'
GROUP BY 1,2,3

UNION ALL

SELECT
 'Total' AS DepartmentName,
 'Total' AS SubDeptName,
 'Total' AS SubSubDeptName,
 sum(STOCKTAKELIST.QTYONHAND) Onhand,
 sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
 sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
 sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM STOCKTAKELIST STOCKTAKELIST
 INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
 INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
 LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
 LEFT JOIN SUBDEPARTMENT 
     ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE
    AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
 LEFT JOIN SUBSUBDEPARTMENT 
     ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE 
    AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE 
    AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
 STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
 STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
 STOCKTAKELIST.IncludeInCount = 'Yes' AND
 STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
 STOCKTAKELIST.Status = 'Complete'

ORDER BY DepartmentName, SubDeptName, subSubDeptName

根據文件CTE:s 被支持。您可以將查詢放在 CTE 中：

WITH T AS (
   SELECT
     DEPARTMENT.DESCRIPTION AS DepartmentName,
     SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
     SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
     sum(STOCKTAKELIST.QTYONHAND) Onhand,
     sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
     sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
     sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
   FROM STOCKTAKELIST STOCKTAKELIST
     INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
     INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
     LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
     LEFT JOIN SUBDEPARTMENT 
         ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE
        AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
     LEFT JOIN SUBSUBDEPARTMENT 
         ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE 
        AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE 
        AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
   WHERE
     STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
     STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
     STOCKTAKELIST.IncludeInCount = 'Yes' AND
     STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
     STOCKTAKELIST.Status = 'Complete'
   GROUP BY 1,2,3
)
SELECT DepartmentName, SubDeptName, SubSubDeptName
    , Onhand, StockCount, Variance, VarianceValue
FROM T
UNION ALL
SELECT 'Total' as DepartmentName
    , 'Total' as SubDeptName
    , 'Total' as SubSubDeptName
    , sum(Onhand), sum(StockCount), sum(Variance), sum(VarianceValue)
FROM T

引用自：https://dba.stackexchange.com/questions/211037

相關問答

Corruption

修復損壞的 Firebird 數據庫

  • August 25, 2021
檢視問答
Constraint

檢測分佈表中的循環關係

  • July 8, 2021
檢視問答
Configuration

啟用系統審計的問題（Firebird 的跟踪和審計服務）

  • July 8, 2021
檢視問答
Performance

使用 WHERE NOT IN 子選擇子句提高性能

  • July 8, 2021
檢視問答
Firebird

Firebird 3.0 中的身份驗證錯誤

  • July 8, 2021
檢視問答
Firebird

SQL 在現有數據前添加零以滿足欄位長度

  • December 25, 2020
檢視問答