请教使用isnul后再l强制转换类型遇到的问题

[yhwobxv2]

做了一个查询统计，语句如下：

SELECT timesCount.user_serial,timesCount.user_lname,timesCount.user_depname,timesCount.days,
timesCount.totalTimes,timesCount.singleTimes,convert(int,isnull(emidiurnalCount.emidiurnalTimes,0)) AS emidiurnalTimes,
((totalTimes-singleTimes)/2-emidiurnalTimes*0.5) AS finallyDays
FROM
(SELECT c.user_serial,d.user_lname,d.user_depname,
datepart(dd,dateadd(dd,-1,convert(datetime,convert(varchar(4),year(dateadd(mm,1,getdate())))+'-'+convert(varchar(2),month(dateadd(mm,1,getdate())))+'-01'))) AS days,
datepart(dd,dateadd(dd,-1,convert(datetime,convert(varchar(4),year(dateadd(mm,1,getdate())))+'-'+convert(varchar(2),month(dateadd(mm,1,getdate())))+'-01')))*2 AS totalTimes,
count(c.lx) AS singleTimes FROM kt_jl c
RIGHT JOIN dt_user d ON c.user_serial = d.user_serial
WHERE c.sj BETWEEN '2014-04-01' and '2014-04-30' AND c.lx IN (1,2)
GROUP BY c.user_serial,d.user_lname,d.user_depname) AS timesCount
left JOIN
(SELECT user_serial,count(user_lname) AS emidiurnalTimes
FROM (SELECT CONVERT(nvarchar,a.sj,108) time,sj,user_serial,user_lname,user_depname,lx FROM
(SELECT jl.sj,jl.user_serial,myuser.user_lname,myuser.user_depname,jl.lx FROM kt_jl jl
LEFT JOIN dt_user myuser ON jl.user_serial=myuser.user_serial WHERE jl.sj BETWEEN
'2014-04-01' and '2014-04-30' AND jl.lx IN (1,2)) a) b WHERE b.time between '12:00:00' AND '17:59:59'
group by user_serial) AS emidiurnalCount
ON timesCount.user_serial=emidiurnalCount.user_serial
ORDER BY timesCount.user_depname asc
但是结果却是:
问题：finallyDays 没有达到我预期的结果

[yoan2014]

你预期的是减去第一个你as 出来的emidiurnalTimes吗？ 不能这样写的，你可以在finallyDays中的emidiurnalTimes的运算式再写一次，如下

SELECT timesCount.user_serial,timesCount.user_lname,timesCount.user_depname,timesCount.days,
timesCount.totalTimes,timesCount.singleTimes,convert(int,isnull(emidiurnalCount.emidiurnalTimes,0)) AS emidiurnalTimes,
((totalTimes-singleTimes)/2-convert(int,isnull(emidiurnalCount.emidiurnalTimes,0))*0.5) AS finallyDays --这边改为表达式
FROM 
(SELECT c.user_serial,d.user_lname,d.user_depname,
datepart(dd,dateadd(dd,-1,convert(datetime,convert(varchar(4),year(dateadd(mm,1,getdate())))+'-'+convert(varchar(2),month(dateadd(mm,1,getdate())))+'-01'))) AS days,
datepart(dd,dateadd(dd,-1,convert(datetime,convert(varchar(4),year(dateadd(mm,1,getdate())))+'-'+convert(varchar(2),month(dateadd(mm,1,getdate())))+'-01')))*2 AS totalTimes,
count(c.lx) AS singleTimes FROM kt_jl c
RIGHT JOIN dt_user d ON c.user_serial = d.user_serial	
WHERE c.sj BETWEEN '2014-04-01' and '2014-04-30' AND c.lx IN (1,2)
GROUP BY c.user_serial,d.user_lname,d.user_depname) AS timesCount
left JOIN 
(SELECT user_serial,count(user_lname) AS emidiurnalTimes
FROM (SELECT CONVERT(nvarchar,a.sj,108) time,sj,user_serial,user_lname,user_depname,lx FROM 
(SELECT jl.sj,jl.user_serial,myuser.user_lname,myuser.user_depname,jl.lx FROM kt_jl jl 
LEFT JOIN dt_user myuser ON jl.user_serial=myuser.user_serial WHERE jl.sj BETWEEN  
'2014-04-01' and '2014-04-30' AND jl.lx IN (1,2)) a) b WHERE b.time between '12:00:00' AND '17:59:59'  
group by user_serial) AS emidiurnalCount
ON timesCount.user_serial=emidiurnalCount.user_serial
ORDER BY timesCount.user_depname asc

[美到心痛]

finallyDays中的emidiurnalTimes是emidiurnalCount.emidiurnalTimes,不是as后的emidiurnalTimes. 你把as emidiurnalTimes换成其它名字就明白了