我正在将列转换为字符串,其中列是选择状态,然后与其他列连接.这是使用CONVERT或CAST时我的混乱.
例：
SELECT employeeID,name,location,(SELECT COUNT(DISTINCT loginsFailed) FROM users WHERE (users.employeedID = userDetails.employeeID) AND (users.startdate = 01-01-2013) as LoginCountFailed,(SELECT COUNT(DISTINCT logins) FROM users WHERE (users.employeedID = userDetails.employeeID) AND (users.startdate = 01-01-2013) as LoginCount FROM userDetails
现在,这个查询工作完美,因为它提供了正确的登录数,并且作为整数失败.但是,我想使用这些整数作为一个字符串,所以我可以一列.有一个原因,为什么这需要一列作为字符串.
我想只有4列,而不是5.我想要的登录列是loginCountFailed / LoginCount.例如：3/12.我需要它作为一个字符串,因为你不能除以0,有时分母是0.
解决方法
对于MSsql-2005中的串联数字,您应该使用CAST
CAST(loginsFailed AS VARCHAR(10)) + '/' + CAST(LoginCount AS VARCHAR(10))
loginsFailed和loginCount上面实际上是你的选择计数不同的片段
我希望这有效
CAST ((SELECT COUNT(DISTINCT loginsFailed) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10)) + '/' + CAST ((SELECT COUNT(DISTINCT logins) FROM users WHERE users.employeedID = userDetails.employeeID AND users.startdate = 01-01-2013) AS VARCHAR(10))
