SQL语句中CASE WHEN以及CAST的运用

时间：2010-11-09 来源：何朝阳

图1

　　新建视图，通过左连接并判断所得的TeacherName是否为空，为空的话直接等于a.TeacherName，否则等于b.TeacherName，如下：

1 SELECT    a.ID, a.ParentID, a.TeacherID, a.OrderID, a.PrizeNumber, a.Remark,
2         CASE WHEN b.TeacherName IS NULL THEN a.TeacherID ELSE b.TeacherName END AS TeacherName
3 FROM    dbo.Web_TeacherIDList AS a LEFT OUTER JOIN
4             dbo.BaseInfo_Teacher AS b ON a.TeacherID = b.TeacherID

　　但是在执行的时候却出现“在将 nvarchar 值 '***' 转换成数据类型 int 时失败。”的错误，百度说是在Sql Server 2005中，默认是以int优先级高，所以需要将int转换成nvarchar，改为下面的SQL语句：

1 SELECT    a.ID, a.ParentID, a.TeacherID, a.OrderID, a.PrizeNumber, a.Remark,
2         CASE WHEN b.TeacherName IS NULL THEN a.TeacherID ELSE b.TeacherName END AS TeacherName
3 FROM    dbo.Web_TeacherIDList AS a LEFT OUTER JOIN
4             dbo.BaseInfo_Teacher AS b ON a.TeacherID = CAST(b.TeacherID AS nvarchar(50))

　　其中的CAST也可以改为CONVERT，测试通过。

图2