select
(select isnull(count(*),0) from RoomContract
where PayModel = '按揭') as '按揭',
(select isnull(count(*),0) from RoomContract
where PayModel = '分期') as '分期',
(select isnull(count(*),0) from RoomContract
where PayModel = '一次性') as '一次性'
from RoomContract
select
ISNULL(sum(Receivable),0.00) as '学费收入',
(select ISNULL(sum(Total),0.00) from dbo.BookSellPay
where BookPayName='教材销售缴费' ) as '教材收入',
(select ISNULL(sum(IncomeMoney),0.00) from dbo.OtherIncome) as '其它收入'
from dbo.PaymentDetails 前面一条查询出来没有数据的时候就是空的,后面一条会赋值为0.00
两条语句一样的,怎么结果就不一样呢
(select isnull(count(*),0) from RoomContract
where PayModel = '按揭') as '按揭',
(select isnull(count(*),0) from RoomContract
where PayModel = '分期') as '分期',
(select isnull(count(*),0) from RoomContract
where PayModel = '一次性') as '一次性'
from RoomContract
select
ISNULL(sum(Receivable),0.00) as '学费收入',
(select ISNULL(sum(Total),0.00) from dbo.BookSellPay
where BookPayName='教材销售缴费' ) as '教材收入',
(select ISNULL(sum(IncomeMoney),0.00) from dbo.OtherIncome) as '其它收入'
from dbo.PaymentDetails 前面一条查询出来没有数据的时候就是空的,后面一条会赋值为0.00
两条语句一样的,怎么结果就不一样呢
--改成这样就一样了,isnull放在查询外面
isnull((select count(*) from RoomContract
where PayModel = '按揭'),0)
要先处理isnull为0再sum
否则有一个null sum就是null了。
(select count(*) from RoomContract
where PayModel = '按揭') as '按揭',
(select count(*) from RoomContract
where PayModel = '分期') as '分期',
(select count(*) from RoomContract
where PayModel = '一次性') as '一次性'
from RoomContract
就行了