mysql - 在查询中使用年份比较数据

Question

我有一个数据，它们是每年记录的，我想在一个 mysql 查询中比较两年(过去一年和今年)的数据

下面是我的表格

成本项目

| cid   |   items   |
| 1     |   A       |
| 2     |   B       |

成本

| cid   | amount    |   year    |
|  1    |   10      |   1       |
|  1    |   20      |   2       |
|  1    |   30      |   1       |

这是我在比较第 1 年和第 2 年时所期望的结果。第 1 年是过去的一年，第 2 年是今年

结果

items   |   pastCost    |   currentCost |
 A      |     10        |       20      |
 A      |     30        |       0       |

但是下面的查询是我使用的给出了一个奇怪的答案。

SELECT 
    IFNULL(ps.`amount`, '0') as  pastCost
    IFNULL(cs.`amount`, '0') as currentCost
FROM
    `Cost Items` b

LEFT JOIN 

    `Cost` ps 
ON 
    b.cID=ps.cID 
        AND 
    ps.Year = 1

LEFT JOIN 
    `Cost` cu 
ON 
    b.cID=cu.cID 
        AND 
    cu.Year =2

这是我查询得到的结果

items   |   pastCost    |   currentCost |
 A      |     10        |       20      |
 A      |     30        |       20      |

请问我做错了什么？感谢您的帮助。

Answer 1

关于您的查询，我遗漏了一些信息；显示的 SQL 文本无法产生该结果。

SELECT 列表中的 items 列没有源，也没有别名为 cs 的表。 (看起来 SELECT 列表中的表达式需要是 cu.amount

除此之外，返回的结果与我们预期的完全一样。从 year=2 返回的每一行都与从 year=1 返回的每一行相匹配。如果 year=1 有三行，year=2 有两行，我们会返回六行...每行 year=1 与 year=2 的每一行“匹配”。

如果 (cid, year) 元组在 Cost 中是唯一的，那么此查询将返回类似于您预期的结果。

  SELECT b.items
       , IFNULL(ps.amount, '0') AS pastCost
       , IFNULL(cu.amount, '0') AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN `Cost` ps
      ON ps.cid  = b.cid 
     AND ps.Year = 1
    LEFT
    JOIN `Cost` cu 
      ON cu.cid  = b.cid 
     AND cu.Year = 2

由于 (cid, year) 不是唯一的，您需要一些额外的列来“匹配”year=1 的单行和 的单行>年=2。

如果表中没有其他列，我们可以使用内联 View 来生成值。我可以说明我们如何让 MySQL 返回一个结果集，就像您展示的那样，这是一种可行的方法，但我认为这并不是您要解决的任何问题的真正解决方案:

  SELECT b.items
       , IFNULL(MAX(IF(a.year=1,a.amount,NULL)),0) AS pastCost
       , IFNULL(MAX(IF(a.year=2,a.amount,NULL)),0) AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN ( SELECT @rn := IF(c.cid=@p_cid AND c.year=@p_year,@rn+1,1) AS `rn` 
                , @p_cid := c.cid AS `cid`
                , @p_year := c.year AS `year`
                , c.amount
             FROM (SELECT @p_cid := NULL, @p_year := NULL, @rn := 0) i
             JOIN `Cost` c
               ON c.year IN (1,2)
            ORDER BY c.cid, c.year, c.amount
         ) a
      ON a.cid = b.cid
   GROUP
      BY b.cid
       , a.rn

类似这样的查询会返回一个看起来像您期望的结果集。但同样，我强烈怀疑这并不是您真正要寻找的结果集。

编辑

OP 留下关于观察到的行为的模糊不清的评论:“上述解决方案不起作用”

那么，让我们检查一下...用一些表创建一个 SQL Fiddle，这样我们就可以测试查询...

SQL Fiddle here http://sqlfiddle.com/#!9/e3d7e/1

create table `Cost Items` (cid int unsigned, items varchar(5));
insert into `Cost Items` (cid, items) values (1,'A'),(2,'B');
create table `Cost` (cid int unsigned, amount int, year int);
insert into `Cost` (cid, amount, year) VALUES (1,10,1),(1,20,2),(1,30,1);

当我们运行查询时，我们得到一个语法错误。 SELECT 列表中的表达式中缺少右括号，很容易修复。

  SELECT b.items
       , IFNULL(MAX(IF(a.year=1,a.amount,NULL)),0) AS pastCost
       , IFNULL(MAX(IF(a.year=2,a.amount,NULL)),0) AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN ( SELECT @rn := IF(c.cid=@p_cid AND c.year=@p_year,@rn+1,1) AS `rn` 
                , @p_cid := c.cid AS `cid`
                , @p_year := c.year AS `year`
                , c.amount
             FROM (SELECT @p_cid := NULL, @p_year := NULL, @rn := 0) i
             JOIN `Cost` c
               ON c.year IN (1,2)
            ORDER BY c.cid, c.year, c.amount
         ) a
      ON a.cid = b.cid
   GROUP
      BY b.cid
       , a.rn

返回:

  items   pastCost  currentCost  
  ------  --------  -----------
  A             10           20
  A             30            0
  B              0            0