SQL SQL比较两行中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25103485/
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
SQL Comparing values in two rows
提问by user3282777
I have the following data of sales for various categories of items:
我有以下各类商品的销售数据：
category year salesVolume
1 2002 45
1 2003 47
2 2002 789
2 2003 908
3 2002 333
3 2003 123
41 2002 111
41 2003 90
Now I want to compare sales volume in the year 2002 to the year 2003, category wise, and write results as:
现在我想比较 2002 年和 2003 年的销售额，类别明智，并将结果写为：
category salesIncreasing?
1 TRUE
2 TRUE
3 FALSE
41 FALSE
Is it possible to do it in SQL. If so please let me know. Actually I am using Impala SQL. Thanks.
是否可以在 SQL 中执行此操作。如果是这样，请告诉我。实际上我正在使用 Impala SQL。谢谢。
回答by David Khuu
SELECT
a.category,
CASE WHEN a.salesVolumes < b.salesVolumes THEN 'TRUE' ELSE 'FALSE' END AS salesIncreasing
FROM MyTable a
INNER JOIN MyTable b ON a.category = b.category
WHERE a.year = 2002
AND b.year = 2003
The idea is to have a single table as a result that let you compare and project the sales into a new data. In order to do this, you join the table with itself, and you use two restrictions in the WHERE clause.
这个想法是有一个单一的表作为结果，让您可以比较销售并将其投影到新数据中。为此，您将表与自身连接起来，并在 WHERE 子句中使用两个限制。
回答by Gordon Linoff
You can do this with conditional aggregation as well as using a join:
您可以使用条件聚合以及使用连接来做到这一点：
select fd.product,
sum(case when year = 2002 then SalesVolume end) as sales_2002,
sum(case when year = 2003 then SalesVolume end) as sales_2003,
(case when sum(case when year = 2002 then SalesVolume end) is null
then 'New2003'
when sum(case when year = 2003 then SalesVolume end) is null
then 'No2003'
when sum(case when year = 2002 then SalesVolume end) > sum(case when year = 2003 then SalesVolume end)
then 'Decreasing'
when sum(case when year = 2002 then SalesVolume end) = sum(case when year = 2003 then SalesVolume end)
then 'Equal'
else 'Increasing'
end) as Direction
from followingdata fd
where year in (2002, 2003)
group by fd.product;
The advantage of this approach over a joinis that it handles allproducts, even those that do not appear in both years.
这种方法相对于 a 的优势join在于它可以处理所有产品，甚至是那些在这两年都没有出现的产品。
