使用联接在我的sql中显示空行 [英] display null rows in my sql using joins

问题描述

select p.partnerid,p.name,count(*) as accounts,
sum(case when c.amount is not null then c.amount else 0 end) as amount,
sum(case when c.netamt is not null then c.netamt else 0 end) as netamt,
sum(case when c.netamt - c.amount is not null then c.netamt - c.amount else 0 end) as interest,
sum(case when c.installment = 20 then c.amount else 0 end) as Twenty,
sum(case when c.installment = 10 then c.amount else 0 end) as Ten,
sum(case when c.installment = 5 then c.amount else 0 end) as Five,
sum(case when c.installment = 2 then c.amount else 0 end) as Two
from partnerinfo p left outer join customerinfo c on p.partnerid = c.partnerid
where (c.startdate is null OR (c.startdate >= '2011-3-15' and c.startdate <= '2012-12-30'))
and (c.partnerid is null or c.partnerid) and p.manager = 1
group by p.partnerid

通过上面的查询，当整个行不存在时，我只能获取空行.

当我从
改变时

With the above query i was able to fetch only null rows / when the whole row does not exist.

When i changed from

where (c.startdate is null OR (c.startdate >= '2011-3-15'...)

TO

TO

where (c.startdate is null OR (c.startdate >= '2012-3-15'...)

但是即使列存在，我也需要以空行的形式获取数据.

为了更好的理解，请查看图片...

http://i46.tinypic.com/155l4cp.jpg [ http://i49.tinypic.com/357ki3b.jpg [

But i need to fetch the data as null rows even when the columns are present.

Hi for a better understanding please see the images...

http://i46.tinypic.com/155l4cp.jpg[^]

http://i49.tinypic.com/357ki3b.jpg[^]

Please help me i was stuck hear.


[edit]Links converted to a proper link - OriginalGriff[/edit]

推荐答案

WHERE子句会影响整行-如您所知.如果要在超出范围的日期时获取数据为零，则需要将条件从WHERE子句移到每个单独的SUM表达式中:

The WHERE clause affects the whole row - as you have noticed. If you want to fetch the data as zero when you have a out-of-range date, then you need to move the condition from the WHERE clause into each of your individual SUM expressions:

sum(case when c.amount is not null then c.amount else 0 end) as amount,
...
where (c.startdate is null OR (c.startdate >= '2011-3-15' and c.startdate <= '2012-12-30'))
and (c.partnerid is null or c.partnerid) and p.manager = 1

变为:

becomes:

sum(case when c.amount is not null and not (c.startdate is null OR (c.startdate >= '2011-3-15' and c.startdate <= '2012-12-30')) then c.amount else 0 end) as amount,
...
where (c.partnerid is null or c.partnerid) and p.manager = 1

这篇关于使用联接在我的sql中显示空行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！