年收入和开支表

Question

我有两个表Income (id，income，date，userid)和 (id,amount,date,userid)。现在我正在尝试创建一个视图，其中包含了一年中收入和金额的总和。而且，它必须是GROUPED by year和userid。

因此，新的表格(视图)必须有收入、开支、年份的总和。现在我被这个代码卡住了。

SELECT
    id,
    i.Prihod,
    e.Rashod,
    YEAR
    YEAR(COALESCE(e.god, i.godi))) AS Godina , 
    users_id
FROM
    (
        SELECT 
            sum(insum) as 'Prihod',
            YEAR(datum) as 'Godi',
            users_id 
        FROM
        GROUP BY users_id
    ) as i
FULL OUTER JOIN
(
    SELECT 
        sum(amount) as 'Rashod',
        YEAR(datum) as 'God',
        users_id
    FROM expense
    GROUP BY users_id

) as e

Answer 1

这是结构化查询语言的结构化部分的工作。从三个子查询(虚拟表)开始。

第一:年份和用户的列表。您需要这样做，因为MySQL没有FULL OUTER JOIN功能。如果您正确地编写了此查询，则它将包含每个用户每年需要的一行。

              SELECT DISTINCT YEAR(datum) Godi, users_id FROM income
              UNION
              SELECT DISTINCT YEAR(datum) Godi, users_id FROM expense

第二，按年份和用户分列的收入汇总。

               SELECT sum(insum) as Prihod,
                      YEAR(datum) as Godi,
                      users_id 
                 FROM income
                GROUP BY YEAR(datum), users_id

第三，按年份和使用者分列的费用总额。

               SELECT sum(amount) as Rashod,
                      YEAR(datum) as God,
                      users_id 
                 FROM expense
                GROUP BY YEAR(datum), users_id

接下来，单独测试这些子查询，以确保您有预期的结果。

最后，将它们连接在一起，就像它们是表一样。

SELECT yr.users_id, yr.Godi, inc.Prihod, exp.Rashod
  FROM (
              SELECT DISTINCT YEAR(datum) Godi, users_id FROM income
              UNION
              SELECT DISTINCT YEAR(datum) Godi, users_id FROM expense
       ) yr
  LEFT JOIN (
               SELECT sum(insum) as Prihod,
                      YEAR(datum) as Godi,
                      users_id 
                 FROM income
                GROUP BY YEAR(datum), users_id
       ) inc ON yr.users_id = inc.users_id AND yr.Godi = inc.Godi 
  LEFT JOIN (
               SELECT sum(amount) as Rashod,
                      YEAR(datum) as God,
                      users_id 
                 FROM expense
                GROUP BY YEAR(datum), users_id
       ) exp ON yr.users_id = exp.users_id AND yr.Godi = exp.God 

这里的查询模式是开发三个子查询。它们中的第一个决定了最终结果集中的行数，因为它包含了每一行所需的一行。第二个和第三个查询对于最终结果集中所需的每一行都有一行，或者可能为零行。那你就把他们连在一起。