我想知道有没有可能提出这样的问题。问题是我有一张表,里面有一些日期的数字。
假设我有3列:日期、值、好/坏
即:
2014-03-03 100 Good
2014-03-03 15 Bad
2014-03-04 120 Good
2014-03-04 10 Bad
我想选择并减去好的坏的:
2014-03-03 85
2014-03-04 110
有可能吗?我想了很多,现在还没有主意。如果我在单独的表中有好值和坏值,那就相当简单了。
最佳答案
诀窍是把你的桌子连接回它自己,如下所示。myTable as A
将只读取Good
行,myTable as B
将只读取Bad
行。然后,根据date
将这些行连接到一个signle行中。
SQL Fiddle Demo
select
a.date
,a.count as Good_count
,b.count as bad_count
,a.count-b.count as diff_count
from myTable as a
inner join myTable as b
on a.date = b.date and b.type = 'Bad'
where a.type = 'Good'
返回的输出:
DATE GOOD_COUNT BAD_COUNT DIFF_COUNT
March, 03 2014 00:00:00+0000 100 15 85
March, 04 2014 00:00:00+0000 120 10 110
另一个方法是使用
Group by
而不是inner join
:select
a.date
,sum(case when type = 'Good' then a.count else 0 end) as Good_count
,sum(case when type = 'Bad' then a.count else 0 end) as Bad_count
,sum(case when type = 'Good' then a.count else 0 end) -
sum(case when type = 'Bad' then a.count else 0 end) as Diff_count
from myTable as a
group by a.date
order by a.date
两种方法产生相同的结果。
关于postgresql - Postgres复杂查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/22144958/