sql - 两列之间的最大差异

Question

需要比较两列值，并希望找出这两列值之间的差异，最后，希望显示该表中存在的所有值，我们找出最大差异

companyname     open    close   dateA                 10      9.8   2015-01-01A                 11      8.2   2015-02-01 A                9.8     6.5    2015-02-04B                 10      8     2015-04-01B                 9.9    9.5    2015-04-15C                 8.7    2.3    2015-02-01

现在我想要显示输出的查询

company name    open    close  difference  dateA                9.8     6.5     3.3       2015-02-04B                 10      8       2        2015-04-01c                 8.7     2.3    6.4       2015-02-01

Answer 1

您只需要一个简单的INNER JOIN 将您的原始表连接到一个子查询，以获得每个公司的最大差异。

SELECT c1.companyname, c1.open, c1.close, c2.maxTime AS difference, c1.date
FROM companies c1
INNER JOIN
(
    SELECT companyname, MAX(open - close) AS maxTime
    FROM companies
    GROUP BY companyname
) c2
ON c1.companyname = c2.companyname AND (c1.open - c1.close) = c2.maxTime

点击下面的链接进行运行演示(由@lc. 提供):

SQLFiddle