SQL SERVER 2008 SELECT语句的情况下

Question

我在VB6应用程序有一个Select语句....

这里是什么样子.....

Dim str As string 

str = "SELECT CompID, Department from tblCompanies " & _ 
     "Where CompID in (123, 234, 345, 456) " & _ 
     "Order by CompID " 

那么我tryign这里要做的是添加一个CASE WHEN语句的WHERE子句 - 基本上我正在寻找添加一个字符串到每个部门名称，具体取决于COMPID。所以，我需要指定这些都是COMPID的，我要选择的话，我想这样做soemthing像

Case when CompID = 123 Then ----ADD "GC" to that Department Name 

我想我需要之前，我打开我的记录与要做到这一点

rs.open str, g_CN, adOpenStatic 

因为一旦打开它，当我尝试编辑它时，似乎给我带来了错误。

所有的一切，如果我的记录是这样的......

Accounting 
Finance 
IT 
R&D 

我想使它看起来像

"GC" - Accounting 
"GC" - Finance 
"BP" - IT 
"DC" - R&D 

Answer 1

更改为，

Dim str As string 

str = "SELECT CompID, CASE WHEN CompID IN (123,234) THEN 'GC' " & _ 
       " WHEN CompID = 345 THEN 'IT' " & _ 
       " WHEN CompID = 456 THEN 'DC' " & _ 
       " ELSE '' END + ' - ' + Department AS Department from tblCompanies " & _ 
     "Where CompID in (123, 234, 345, 456) " & _ 
     "Order by CompID " 

Answer 2

试试这个：

str = "SELECT CompID, CASE WHEN CompID = 123 THEN '""GC"" - ' ELSE '' END + Department As Department from tblCompanies " & _ 
     "Where CompID in (123, 234, 345, 456) " & _ 
     "Order by CompID "