SQL 如何使用sql查询在同一列中减去两行的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26817139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I subtract two row's values within same column using sql query?
提问by Talk2Nit
I want to display the subtractionof two values from twodifferent rows using a SQLquery.
我想使用查询显示subtraction来自two不同行的两个值SQL。
This is the table structure:
这是表结构：
------------------------------------
id | name | sub1 | sub2 | date
------------------------------------
1 | ABC | 50 | 75 | 2014-11-07
2 | PQR | 60 | 80 | 2014-11-08
I want to subtract date 2014-11-08 subject marks from date 2014-11-07.
我想从日期 2014-11-07 中减去日期 2014-11-08 的主题标记。
Output should be like as
输出应该像
| sub1 | sub2 |
---------------
| 10 | 5 |
回答by Gordon Linoff
You can use a join to get the rows and then subtract the values:
您可以使用连接来获取行，然后减去值：
SELECT(t2.sub1 - t1.sub1) AS sub1, (t2.sub2 - t1.sub2) AS sub2
FROM table t1 CROSS JOIN
table t2
WHERE t1.date = '2014-11-08' AND t2.id = '2014-11-07';
回答by Bruce Dunwiddie
I feel like you're leaving out an important part of your actual needs where you'll probably want to group by some specific field and return corresponding values, so the answer will be kind of limited. You can double reference the table like the example above, but it's usually much better if you can somehow only reference the table only once and remove the need for index lookups, bookmark lookups, etc. You can usually use simple aggregates or windowed aggregates to accomplish this.
我觉得您遗漏了实际需求的一个重要部分，您可能希望按某些特定字段进行分组并返回相应的值，因此答案会受到限制。您可以像上面的示例一样双重引用该表，但如果您能以某种方式只引用该表一次并且不需要索引查找、书签查找等，通常会更好。您通常可以使用简单聚合或窗口聚合来完成这个。
SELECT
MAX(sub1) - MIN(sub1) AS sub1,
MAX(sub2) - MIN(sub2) AS sub2
FROM
dbo.someTable;
回答by Carolyn Conway
Cross joins can be difficult to work with because they relate data in ways that are usually unintuitive. Here's how I would do it instead, with the simple, default, INNER JOIN:
交叉联接可能难以使用，因为它们以通常不直观的方式关联数据。下面是我将如何做到这一点，使用简单的默认值INNER JOIN：
WITH day1_info AS
(SELECT sub1, sub2
FROM mytable)
SELECT
day2_info.sub1 - day1_info.sub1 AS sub1_difference,
day2_info.sub2 - day1_info.sub2 AS sub2_difference,
FROM
mytable AS day2_info JOIN day1_info
ON day1_info.date = '2014-11-07'
AND day2_info.date = '2014-11-08'
If you'd like to do this for multiple sets of dates, you can do that too. Just change the JOINstatement slightly. (Note that in this case, you may also want to SELECTone of the dates as well, so that you know which time period each result applies to.)
如果您想对多组日期执行此操作，您也可以这样做。只需JOIN稍微更改声明即可。（请注意，在这种情况下，您可能还需要SELECT其中一个日期，以便您知道每个结果适用于哪个时间段。）
WITH day1_info AS
(SELECT sub1, sub2
FROM mytable)
SELECT
day2_info.date,
day2_info.sub1 - day1_info.sub1 AS sub1_difference,
day2_info.sub2 - day1_info.sub2 AS sub2_difference,
FROM
mytable AS day2_info JOIN day1_info
ON (day1_info.date::timestamp + '1 day') = day2_info.date::timestamp
