mysql - 如何找到MySQL查询结果中两行的差异

Question

我有以下 MySQL 查询:

SET @rowno= 0;
SELECT @rowno:=@rowno+1 AS `row`,`year`,`year_average` FROM(
/*get the average from the start of the 10 year period and the current period*/  

SELECT year,AVG(value) AS year_average FROM
/* get all records that have technical and trade school industry code */
(SELECT * FROM `allcesseries`
   WHERE series_id LIKE concat('%',(SELECT industry_code FROM `ceindustry` WHERE industry_name LIKE      '%Technical and trade schools%'),'%')) AS trade_schools
WHERE trade_schools.year = (2014-10) OR trade_schools.year = 2014
GROUP BY year) AS t

返回值

row | year | year_average
 1    2004   76.24
 2    2014   99.9

我似乎不知道如何获得第 1 行的年度平均值和第 2 行的年度平均值之间的差异。

Answer 1

使用 MySQL

SELECT (
  (SELECT AVG(value)
  FROM allcesseries
  WHERE series_id LIKE concat('%',(SELECT industry_code FROM `ceindustry` WHERE industry_name LIKE '%Technical and trade schools%'),'%')
    AND year = 2014)
  -
  (SELECT AVG(value)
  FROM allcesseries
  WHERE series_id LIKE concat('%',(SELECT industry_code FROM `ceindustry` WHERE industry_name LIKE '%Technical and trade schools%'),'%')
    AND year = 2004)
) AS difference
;

应该可以工作。请参阅proof of concept 。如果在您的情况下不起作用(或需要调整)，请提供SQL Fiddle带有表结构和示例数据。