在 SQL Select 中将列转换为字符串

本文介绍了在 SQL Select 中将列转换为字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我希望将一列转换为字符串，其中该列是一个选择语句，然后与另一列连接.这就是我在使用 CONVERT 或 CAST 时发生混淆的地方.

I am looking to convert a column to string where the column is a select statment and then concat with another column. This is where my confusion occurs when using CONVERT or 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

现在，这个查询非常完美，因为它提供了正确的登录次数和失败的整数.但是，我想将这些整数用作字符串，以便我可以使用一列.这需要作为字符串的一列是有原因的.

Now, this query works perfect in that is provides the correct number of logins and failed as integers. However, I want to use these integer as a string so i can one column. There is a reason why this needs to be one column as string.

我想要只有 4 列，而不是 5.我想要的登录列是 loginCountFailed/LoginCount.例如:3/12.我需要它作为一个字符串，因为你不能除以 0，有时分母是 0.

I want to have only 4 columns, not 5. The login column I want to have is loginCountFailed/LoginCount. For example: 3/12. I need it as a string because you cannot divide by a 0 and there are times where the denominator is 0.

推荐答案

对于 MSSQL-2005 中的连接数字，您应该使用 CAST

For concatanating numbers in MSSQL-2005 you should use CAST

CAST(loginsFailed AS VARCHAR(10)) + '/' + CAST(LoginCount AS VARCHAR(10))

loginsFailed 和 loginCount 上面实际上是你的 select count distinct 片段

loginsFailed and loginCount above is actually your select count distinct fragments

我希望这有效

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))