主页 / user-194932

Core's questions

Martin Hope
Core
Asked: 2019-11-25 23:51:37 +0800 CST
  • -1

我有一张表格（请参阅下面的结构），我可以在其中跟踪所有库存进出。现在我想从这个表中生成一个报告，根据提供的日期范围查找opening_stock、purchased、purchased_return、sales、sales_returned、damaged_inventory 。

询问：

SELECT ITEM_ID,  

(SELECT (SUM(PURCHASE_QUANTITY) - SUM(SALE_QUANTITY)) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE < '2019-09-31' AND ITEM_ID = l.ITEM_ID) AS OPENING
, (SELECT COALESCE(SUM(PURCHASE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'PURCHASE') AS PURCHASE
, (SELECT COALESCE(SUM(SALE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'PURCHASE RETURN') AS PURCHASE_RETURN
, (SELECT COALESCE(SUM(SALE_QUANTITY),0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'SALE') AS SALE
, (SELECT COALESCE(SUM(PURCHASE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'SALE RETURN') AS SALE_RETURN
, (SELECT COALESCE(SUM(SALE_QUANTITY), 0) FROM INV_ITEM_LEDGER WHERE ENTRY_DATE >= '2019-09-31' AND ITEM_ID = l.ITEM_ID AND REF_TYPE = 'DMG') AS DAMAGED_INVENTORY
FROM INV_ITEM_LEDGER l
GROUP BY ITEM_ID

当我对47,619条记录（当前查询总数）运行此查询时，系统挂起并停止响应。

表结构：

CREATE TABLE INV_ITEM_LEDGER (
  ITEM_LEDGER_ID     INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ITEM_ID            INTEGER,
  REF_ID             INTEGER,
  REF_TYPE           VARCHAR(20),
  PURCHASE_QUANTITY  DECIMAL(18,4),
  SALE_QUANTITY      DECIMAL(18,4),
  DESCRIPTION        VARCHAR(512),
  BATCH_NO           VARCHAR(20),
  EXPIRY             VARCHAR(20),
  STOCK_ID           INTEGER,
  BRANCH_CODE        VARCHAR(20),
  LAST_SYNCED        VARCHAR(20),
  ENTRY_DATE         VARCHAR(20),
  LAST_UPDATED       VARCHAR(20),
  ENTRY_DATE2        VARCHAR(20),
  /* Keys */
  PRIMARY KEY (ITEM_LEDGER_ID)
);

CREATE INDEX INV_ITEM_LEDGER_INDEX01
  ON INV_ITEM_LEDGER
  (ITEM_LEDGER_ID, ITEM_ID);

有什么可以增强查询的吗？

performance firebird
Martin Hope
Core
Asked: 2019-11-24 03:14:05 +0800 CST
  • 2

我在 Delphi 101.Berlin 中使用 FireDAC，我想IDENTITY DATA TYPE在执行插入查询后获取最后插入的列 id，但是返回值为空。

询问：INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES ('TEST') returning ACCOUNT_HEAD_ID;

表结构

CREATE TABLE ACC_ACCOUNT_HEAD (
 ACCOUNT_HEAD_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_TITLE    VARCHAR(208),
  PRIMARY KEY (ACCOUNT_HEAD_ID)
);

注意：我知道生成器可以，但我想知道IDENTITY列是否可以。

完整的德尔福代码：

var
aQry : TFDQuery;
begin
aQry := TFDQuery.Create(Self);
aQry.Connection := frmMain.aCon;

 aQry.SQL.Text :=
      'INSERT INTO ACC_ACCOUNT_HEAD (ACCOUNT_HEAD_TITLE) VALUES (:P1) returning ACCOUNT_HEAD_ID';
    aQry.ParamByName('P1').AsString := 'Testing';
    aQry.ExecSQL; // or Should I use aQry.Open(); to get returned value???
firebird delphi