我想知道有没有可能提出这样的问题。问题是我有一张表,里面有一些日期的数字。
假设我有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/

10-10 13:40
查看更多