mysql - 如何比较同一个表中同一列的数据

Question

我需要比较同一个表的同一列中2017年和2016年的数据。

现在我使用 2 个查询:

第一个查询:

  SELECT 
      SUM(a.netamt) AS netamt2017, b.store_name, c.budget
  FROM 
      site_sales a 
  JOIN 
      site_store b ON b.storenum = a.storenum 
  JOIN 
      site_kpimthslsbgt c ON b.storenum = c.storenum 
  WHERE 
      c.busidate = '2017-01' 
      AND a.busidate >= '2017-01-01' 
      AND a.busidate <= '2017-01-15'
  GROUP BY 
      a.storenum

这适用于 find netamt 2017

第二个查询:

  SELECT 
      SUM(a.netamt) AS netamt2016, b.store_name, c.budget
  FROM 
      site_sales a 
  JOIN 
      site_store b ON b.storenum = a.storenum 
  JOIN  
      site_kpimthslsbgt c ON b.storenum = c.storenum 
  WHERE 
      c.busidate = '2017-01' 
      AND a.busidate >= '2016-01-01' 
      AND a.busidate <= '2016-01-15'
  GROUP BY 
      a.storenum

这用于查找“Netamt 2016”。

我需要组合这两个查询来形成此表:

Here is example for the output

Answer 1

只需使用 sum(case when ... then ... else ... end) 语法:

SELECT
    SUM(CASE WHEN a.busidate BETWEEN '2016-01-01' AND '2016-01-15' THEN a.netamt ELSE 0 END) as netamt2016,
    SUM(CASE WHEN a.busidate BETWEEN '2017-01-01' AND '2017-01-15' THEN a.netamt ELSE 0 END) as netamt2017,
    b.store_name,
    c.budget
FROM site_sales a JOIN site_store b ON b.storenum = a.storenum 
JOIN site_kpimthslsbgt c ON b.storenum = c.storenum 
WHERE c.busidate = '2017-01'
GROUP BY a.storenum

注意:因为您使用了GROUP BY、b.store_name、c.budget 没有聚合，所以这两列没有意义。