我试图建立一个基本的数据库,帮助筛选许多CSV银行交易。
我已经从多个账户的多个表中导入了我的所有交易,这些账户有“扣减”列,我将其标记为“假”或“真”
我正在尝试做一个“创建视图”来将多个表中的源代码转换为一个表中的“演绎”。
巴西萼;
有收入列的多个表中的银行交易记录为“TRUE”
错误是-查询1错误:错误:列“收入”指定了更多
不止一次
我已经尝试了一天的各种代码,但似乎无法让它发挥作用。

CREATE VIEW "Deductions" AS
SELECT
    "EverydaySTG"."Income",
    "EverydaySTG"."Deduction",
    "EverydaySTG"."Date",
    "EverydaySTG"."Amount",
    "EverydaySTG"."Payee",
    "CC WTPC"."Income",
    "CC WTPC"."Deduction",
    "CC WTPC"."Date",
    "CC WTPC"."Amount"
FROM
    "EverydaySTG",
    "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or(
        "CC WTPC"."Deduction" = 'TRUE' ::text
);

最佳答案

报表中有多个收入栏:"EverydaySTG"."Income""CC WTPC"."Income"。替换..:

CREATE VIEW "Deductions"
AS SELECT
    "EverydaySTG"."Income",
    "EverydaySTG"."Deduction",
    "EverydaySTG"."Date",
    "EverydaySTG"."Amount",
    "EverydaySTG"."Payee",
    "CC WTPC"."Income",
    "CC WTPC"."Deduction",
    "CC WTPC"."Date",
    "CC WTPC"."Amount"
FROM
    "EverydaySTG", "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or ( "CC WTPC"."Deduction" = 'TRUE' ::text );

... 签署人:
CREATE VIEW "Deductions"
AS SELECT
    "EverydaySTG"."Income" AS income_a,
    "EverydaySTG"."Deduction" AS deduction_a,
    "EverydaySTG"."Date" AS date_a,
    "EverydaySTG"."Amount" AS amount_a,
    "EverydaySTG"."Payee",
    "CC WTPC"."Income" AS income_b,
    "CC WTPC"."Deduction" AS deduction_b,
    "CC WTPC"."Date" AS date_b,
    "CC WTPC"."Amount" AS amount_b
FROM "EverydaySTG", "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or( "CC WTPC"."Deduction" = 'TRUE' ::text );

OP comments“结果是事务已水平填充。而不是收入、扣除额、日期、金额等干净的单行。有没有办法保持原始行的完整性,从而使事务垂直堆叠?”.
我知道他们想要一个联合查询:
CREATE TABLE "EverydaySTG" (
    "Income" INTEGER
    "Deduction" CHARACTER VARYING
    "Date" DATE
    "Amount" INTEGER
    "Payee" CHARACTER VARYING
);

CREATE TABLE "CC WTPC" (
    "Income" INTEGER
    "Deduction" CHARACTER VARYING
    "Date" DATE
    "Amount" INTEGER
);

 
CREATE VIEW "Deductions_horizontal"
AS SELECT
    "EverydaySTG"."Income" AS income_a,
    "EverydaySTG"."Deduction" AS deduction_a,
    "EverydaySTG"."Date" AS date_a,
    "EverydaySTG"."Amount" AS amount_a,
    "EverydaySTG"."Payee",
    "CC WTPC"."Income" AS income_b,
    "CC WTPC"."Deduction" AS deduction_b,
    "CC WTPC"."Date" AS date_b,
    "CC WTPC"."Amount" AS amount_b
FROM "EverydaySTG", "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or( "CC WTPC"."Deduction" = 'TRUE' ::text );

 
CREATE VIEW "Deductions_vertical" AS
    SELECT
        "EverydaySTG"."Income",
        "EverydaySTG"."Deduction",
        "EverydaySTG"."Date",
        "EverydaySTG"."Amount",
        "EverydaySTG"."Payee",
    FROM "EverydaySTG"
    WHERE "EverydaySTG"."Deduction" = 'TRUE'
    UNION
    SELECT
        "CC WTPC"."Income",
        "CC WTPC"."Deduction",
        "CC WTPC"."Date",
        "CC WTPC"."Amount",
        NULL AS "Payee"
    FROM "CC WTPC"
    WHERE "CC WTPC"."Deduction" = 'TRUE';

10-06 11:06