数据库查询为空数据问题
0
悬赏园豆：80
[已关闭问题]
关闭于 2011-06-22 16:51
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
两条查询语句一样的 但查询出来的结果不一样
第一条
按揭 分期 一次性
没数据的话 就为空 isnull 函数好像没用，但第二条就可以
学费收入 教材收入 其它收入
0.00 0.00 0.00
怎样让第一条语句和第二条查询出来的结果一样呢
所有回答(6)
0
是不是你过滤条件的字段有为“”，
0
这个当然是没有结果了，Count统计的是记录个数，如果显示0，那就表示有记录了！
你可以查询(前提是没有记录)
Select 0 from RoomContract
结果肯定是什么也没有。
那应该怎么查？
0
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（你将from roomcontract这句去掉）
直接select
(select count(*) from RoomContract
where PayModel = '按揭') as '按揭',
(select count(*) from RoomContract
where PayModel = '分期') as '分期',
(select count(*) from RoomContract
where PayModel = '一次性') as '一次性'
不知道你是不是想要这个结果
0
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
你感觉上下查询一样吗、
就是一个是count 一个是sum 难道用isnull有区别吗
0
0
你这个是没有条件的查询，所以会把表里面所有的行都会展现。
第一个SQL是因为RoomContract这个表没有数据，所以显示为空。如果里面有一条数据，则会显示一行三列的0；如果有两条，则有两行三列的0
第二个SQL估计PaymentDetails这个表里面有条数据，而且Receivable是空的。所以会出现第二个情况。
如果要一样结果，则第一个SQL可以查询dual（不知道SQL SERVER是什么表），得到000的结果
