mysql得到差异而不是总和 [英] mysql get difference instead of SUM

问题描述

我有以下查询:

SELECT SUM(P_QTY) 
FROM rankhistory
WHERE P_ID= '1'
AND RH_DATE>=1438556400
AND RH_DATE<1438642800 

上面的查询返回268

结果集包含P_QTY的两个元素，分别是160和108

The result set contains two elements of P_QTY which are 160 and 108

现在我想要的是能够接收差异而不是总和，所以我想要查询返回的是52，如何通过sql查询实现该目标? 请注意，子查询可以返回多个结果，其目的是获得总更改.例如，如果查询返回168 160 150，则结果应为18.

Now what I want is be able to receive the difference instead of the sum, so what I want my query to return is 52, how can I achieve that through sql query? Please note that the subquery can return more than one result, and the intended is get the total change. For example if query returns 168 160 150, the result should be 18.

推荐答案

没有汇总函数可用于区别，但是由于您确切知道要使用的行，因此可以选择每个值作为自己的子查询，然后从中减去两列在一个选择语句中彼此重叠.

There's no aggregate function for difference, but since you know exactly which rows to use, you can select each value as its own subquery, then subtract the two columns from one another in a single select statement.

SELECT a.op, b.op, a.op - b.op as diff
FROM (SELECT 10 as op) a
JOIN (SELECT 8 as op) b

按照您的模式表示，可能看起来像这样:

Expressed in accordance with your schema, it would probably look like this:

SELECT a.op, b.op, a.op - b.op as diff
FROM (SELECT P_QTY as op FROM rankhistory WHERE P_QTY = 160) a
JOIN (SELECT P_QTY as op FROM rankhistory WHERE P_QTY = 108) b

但是，要在应用程序中定期使用此方法，您将需要根据ID或其他容易选择且有意义的东西来处理它.

To use this approach regularly in an application, however, you'll want to handle it based on ID's or something else easily selectable and meaningful.

不过，听起来您还想要其他东西.也许您对日期范围内的最大值和最小值之间的差异感兴趣?

It sounds like you want something else, though. Perhaps you're interested in the difference between max and min during a date range?

SELECT MAX(P_QTY) - MIN(P_QTY) as diff
FROM rankhistory
WHERE rh_date BETWEEN '1438556400' AND '1438642800'

这篇关于mysql得到差异而不是总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！