sql语句如下:
select a.username name,a.times date,isnull(a.count_1,0) count1,isnull(b.count_2,0) count2,--isnull(cast(cast(isnull(b.count_2*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per1,isnull(c.count_3,0) count3,--isnull(cast(cast(isnull(c.count_3*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per2,isnull(d.count_4,0) count4,--isnull(cast(cast(isnull(d.count_4*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per3
from
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_1
from t_user u,t_imageorder i
where i.DEALER=u.id and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)a
left join
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_2
from t_user u,t_imageorder i
where i.DEALER=u.id and i.SERIAL_NO_COUNT=1 and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)b
on a.username=b.username and a.times=b.times left join
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_3
from t_user u,t_imageorder i
where i.DEALER=u.id and i.B_A_COUNT=1 and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)c
on c.username=b.username and c.times=b.times left join
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_4
from t_user u,t_imageorder i
where i.DEALER=u.id and i.NAME_CHECK_COUNT=1 and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)d
on d.username=c.username and d.times=c.times
现在查出的结果是正确的如:
name date count1 count2 count3 count4
________________________________________________________
admin 2010-10-10 6 6 0 0
admin 2010-10-11 0 0 0 0
FHW_NGCC 2010-10-10 0 0 0 0
FHW_NGCC 2010-10-11 0 0 0 0但是去掉注释加入百分比后就出现了下面的数据:
name date count1 count2 per1 count3 per2 count4 per3
__________________________________________________________________________________________________
FSG8YY000042 2010-10-10 0 0 0.00% 0 0.00% 0 0.00%
FSG3YX000004 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG4YY000128 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG8YY000048 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG4DB000014 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG1YY000199 2010-10-10 0 0 0.00% 0 0.00% 0 0.00%
FSG2YY000127 2010-10-10 0 0 0.00% 0 0.00% 0 0.00%如admin和其他的一些数据就不见了,所以我觉得很奇怪,请高手赐教,为什么会出现这种情况!
select a.username name,a.times date,isnull(a.count_1,0) count1,isnull(b.count_2,0) count2,--isnull(cast(cast(isnull(b.count_2*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per1,isnull(c.count_3,0) count3,--isnull(cast(cast(isnull(c.count_3*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per2,isnull(d.count_4,0) count4,--isnull(cast(cast(isnull(d.count_4*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per3
from
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_1
from t_user u,t_imageorder i
where i.DEALER=u.id and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)a
left join
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_2
from t_user u,t_imageorder i
where i.DEALER=u.id and i.SERIAL_NO_COUNT=1 and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)b
on a.username=b.username and a.times=b.times left join
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_3
from t_user u,t_imageorder i
where i.DEALER=u.id and i.B_A_COUNT=1 and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)c
on c.username=b.username and c.times=b.times left join
(
select u.username username,convert(varchar(10),i.SCAN_TIME,121) times,count(i.id) count_4
from t_user u,t_imageorder i
where i.DEALER=u.id and i.NAME_CHECK_COUNT=1 and convert(varchar(10),i.SCAN_TIME,121) between '2010-10-07' and '2010-10-12'
group by all u.username,convert(varchar(10),i.SCAN_TIME,121)
)d
on d.username=c.username and d.times=c.times
现在查出的结果是正确的如:
name date count1 count2 count3 count4
________________________________________________________
admin 2010-10-10 6 6 0 0
admin 2010-10-11 0 0 0 0
FHW_NGCC 2010-10-10 0 0 0 0
FHW_NGCC 2010-10-11 0 0 0 0但是去掉注释加入百分比后就出现了下面的数据:
name date count1 count2 per1 count3 per2 count4 per3
__________________________________________________________________________________________________
FSG8YY000042 2010-10-10 0 0 0.00% 0 0.00% 0 0.00%
FSG3YX000004 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG4YY000128 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG8YY000048 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG4DB000014 2010-10-11 0 0 0.00% 0 0.00% 0 0.00%
FSG1YY000199 2010-10-10 0 0 0.00% 0 0.00% 0 0.00%
FSG2YY000127 2010-10-10 0 0 0.00% 0 0.00% 0 0.00%如admin和其他的一些数据就不见了,所以我觉得很奇怪,请高手赐教,为什么会出现这种情况!
最外层isnull多余,其他的看着也正常。
rtrim(cast(isnull(b.count_2,0)*100.0/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2))) + '%'--试试
--isnull(cast(cast(isnull(b.count_2*100.0,0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar) + '%','0%') per1,
看看返回什么不就清楚了么?
没那么诡异,问题出在细微的地方了。
是什么类型?
float还是int
declare @count_4 int
declare @count_1 int
set @count_4=1
set @count_1=4
select isnull(cast(cast(isnull(@count_4*100.0,0)/(case @count_1 when 0 then 1 else @count_1 end) as decimal(18,2)) as varchar) + '%','0%') per3 per3
-------------------------------
25.00%declare @count_4 int
declare @count_1 int
set @count_4=null --设为null
set @count_1=4
select isnull(cast(cast(isnull(@count_4*100.0,0)/(case @count_1 when 0 then 1 else @count_1 end) as decimal(18,2)) as varchar) + '%','0%') per3 (1 行受影响)per3
-------------------------------
0.00%(1 行受影响)cast(cast(isnull(b.count_2,0)*100.0)/(case a.count_1 when 0 then 1 else a.count_1 end) as decimal(18,2)) as varchar(18)) + '%' per1,
--最外层的isnull不起作用的。