sqlite SQL查询获取总和

fivyi3re 于 2023-02-05 发布在 SQLite

关注(0)|答案(1)|浏览(217)

请帮助我解决这个问题：我有疑问：

SELECT drugs_shipment.drug_id,
    drugs_drug.name AS drug_name,
    drugs_drugunit.name AS drug_unit,
  drugs_shipment.initial_amount  - SUM(IFNULL(drugs_movement.amount, "0")) OVER (PARTITION BY drugs_shipment.id) AS total_amount
  FROM drugs_shipment
    JOIN drugs_drug ON drugs_shipment.drug_id = drugs_drug.id
    JOIN drugs_drugunit ON drugs_drug.unit_id = drugs_drugunit.id
    LEFT JOIN drugs_movement ON (drugs_movement.shipment_id = drugs_shipment.id AND drugs_movement.DATE < "2025-12-11" )
WHERE drugs_shipment.date_of_comming < "2025-12-11"
    AND (drugs_shipment.date_of_run_out IS NULL OR drugs_shipment.date_of_run_out > "2025-12-11")

接下来，我需要具有相同drugs_shipment.drug_id的字段中的total_amount的总和。什么是whrong？如何解决此问题？
但这行不通：

SELECT drugs_shipment.drug_id,
    drugs_drug.name AS drug_name,
    drugs_drugunit.name AS drug_unit,
    SUM (drugs_shipment.initial_amount  - SUM(IFNULL(drugs_movement.amount, "0")) OVER (PARTITION BY drugs_shipment.id)) AS total_amount
FROM drugs_shipment
    JOIN drugs_drug ON drugs_shipment.drug_id = drugs_drug.id
    JOIN drugs_drugunit ON drugs_drug.unit_id = drugs_drugunit.id
    LEFT JOIN drugs_movement ON (drugs_movement.shipment_id = drugs_shipment.id AND drugs_movement.DATE < "2025-12-11" )
WHERE drugs_shipment.date_of_comming < "2025-12-11"
    AND (drugs_shipment.date_of_run_out IS NULL OR drugs_shipment.date_of_run_out > "2025-12-11")
GROUP BY drugs_shipment.drug_id

sqlite

来源：https://stackoverflow.com/questions/75306397/sql-query-get-sum-of-sums

1条答案

按热度按时间

pw136qt21#

不允许同时使用窗口函数（OVER子句）和分组（GROUP BY子句）。需要使用另一个窗口函数或嵌套查询来执行此操作。

SELECT drug_id
      ,drug_name
      ,drug_unit
      ,SUM(total_amount) OVER (PARTITION BY drug_id)
FROM
(
    SELECT drugs_shipment.drug_id,
           drugs_drug.NAME AS drug_name,
           drugs_drugunit.NAME AS drug_unit,
           drugs_shipment.initial_amount - Sum(Ifnull(drugs_movement.amount, "0")) OVER (partition BY drugs_shipment.id) AS
           total_amount
    FROM   drugs_shipment
           JOIN drugs_drug
             ON drugs_shipment.drug_id = drugs_drug.id
           JOIN drugs_drugunit
             ON drugs_drug.unit_id = drugs_drugunit.id
           LEFT JOIN drugs_movement
                  ON ( drugs_movement.shipment_id = drugs_shipment.id
                       AND drugs_movement.date < "2025-12-11" )
    WHERE  drugs_shipment.date_of_comming < "2025-12-11"
           AND ( drugs_shipment.date_of_run_out IS NULL
                  OR drugs_shipment.date_of_run_out > "2025-12-11" ) 
) DS

赞(0）回复(0）举报 2023-02-05

我来回答

sqlite SQL查询获取总和

1条答案

相关问题

热门标签

最新问答