1.
2.
3.
WHEN THEN
WHEN THEN
ELSE
END
-- ------------
select count( *) as ct , CASE
WHEN (Pro_state = 1) THEN ' 待审 '
WHEN (Pro_state = 2) THEN ' 已审 '
WHEN (Pro_state = 3) THEN ' 办理中 '
WHEN (Pro_state = 4) THEN ' 已申退 '
WHEN (Pro_state = 5) THEN ' 来信 '
WHEN (Pro_state = 6) THEN ' 办理完成 '
WHEN (Pro_state = 7) THEN ' 未予立案 '
end as pro_state
from vw_proposals_query where 1 = 1 GROUP BY pro_state
结果:
ct pro -state
30 办理中
7 来信
1 办理完成
5 未予立案
解读:
查询总数和pro_state 当 Pro_state = 1 时以 ' 待审 ' 替代表示
当 Pro_state = 2 时以 ' 已审 ' 替代表示
当 Pro_state = 3 时以 ' 办理中 ' 替代表示
。。。。。。
4.
今天下午因为这个原因做了一个下午,因为,项目中,联系人详细信息这一个页面中,联系人的性别在数据库字段的保存,男的用0表示,女的用1表示,所以,如果直接就读出来绑定到DataList中,性别就显示成0或1了。所以需要将Sql语句进行改造。先看看我原先的做法,这样做,组长叫我重做了,要叫我要CASE WHEN来写。
不用CASE WHEN 的做法。源代码如下:
string sql =" select * from CONTACTPERSON ,CUSTOMER where CONTACTPERSONID = ' "+strContactid+" ' and CONTACTPERSON.CUSTOMERID =CUSTOMER.CUSTOMERID";
DataSet ds =new DataSet();
ds =Common.GetDSCommon(sql);
if(ds.Tables [ 0 ].Rows [ 0 ][ "CONTACTPERSONSEX" ].ToString() ==" 0")
{
ds.Tables [ 0 ].Rows [ 0 ][ "CONTACTPERSONSEX" ] ="男";
}
else
{
ds.Tables [ 0 ].Rows [ 0 ][ "CONTACTPERSONSEX" ] ="女";
}
使用CASE WHEN做法。
string sql =" select CUSTOMER. *,CONTACTPERSON.CONTACTPERSONNAME,CONTACTPERSON.CONTACTPERSONBIRTHDAY,CONTACTPERSON.CONTACTPERSONPOSITION,CONTACTPERSON.CONTACTPERSONDEPTNAME,";
sql +="CONTACTPERSON.TELEPHONE,CONTACTPERSON.MAIL,CONTACTPERSON.CITY,CONTACTPERSON.PROVINCE,CONTACTPERSON.POSTCODE,CONTACTPERSON.COUNTRY,CONTACTPERSON.MEMO,";
sql +="CONTACTPERSON.DIRECTOR,";
sql +=" case when CONTACTPERSON.CONTACTPERSONSEX = ' 0 ' then ' 男 ' when CONTACTPERSON.CONTACTPERSONSEX = ' 1 ' then ' 女 ' end as CONTACTPERSONSEX from CUSTOMER ,CONTACTPERSON where CONTACTPERSONID = ' "+strContactid+" ' and CONTACTPERSON.CUSTOMERID =CUSTOMER.CUSTOMERID";
可以看出这样明显加长了,SQL语句,这样做有什么好处呢?
不用CASE WHEN 的做法。源代码如下:
string sql =" select * from CONTACTPERSON ,CUSTOMER where CONTACTPERSONID = ' "+strContactid+" ' and CONTACTPERSON.CUSTOMERID =CUSTOMER.CUSTOMERID";
DataSet ds =new DataSet();
ds =Common.GetDSCommon(sql);
if(ds.Tables [ 0 ].Rows [ 0 ][ "CONTACTPERSONSEX" ].ToString() ==" 0")
{
ds.Tables [ 0 ].Rows [ 0 ][ "CONTACTPERSONSEX" ] ="男";
}
else
{
ds.Tables [ 0 ].Rows [ 0 ][ "CONTACTPERSONSEX" ] ="女";
}
使用CASE WHEN做法。
string sql =" select CUSTOMER. *,CONTACTPERSON.CONTACTPERSONNAME,CONTACTPERSON.CONTACTPERSONBIRTHDAY,CONTACTPERSON.CONTACTPERSONPOSITION,CONTACTPERSON.CONTACTPERSONDEPTNAME,";
sql +="CONTACTPERSON.TELEPHONE,CONTACTPERSON.MAIL,CONTACTPERSON.CITY,CONTACTPERSON.PROVINCE,CONTACTPERSON.POSTCODE,CONTACTPERSON.COUNTRY,CONTACTPERSON.MEMO,";
sql +="CONTACTPERSON.DIRECTOR,";
sql +=" case when CONTACTPERSON.CONTACTPERSONSEX = ' 0 ' then ' 男 ' when CONTACTPERSON.CONTACTPERSONSEX = ' 1 ' then ' 女 ' end as CONTACTPERSONSEX from CUSTOMER ,CONTACTPERSON where CONTACTPERSONID = ' "+strContactid+" ' and CONTACTPERSON.CUSTOMERID =CUSTOMER.CUSTOMERID";
可以看出这样明显加长了,SQL语句,这样做有什么好处呢?
2.
SQL条件控制(
case
when...
then...
else...
end)
我的语句:(SQL 2000)
1:
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime)) else DATEDIFF(day,ComeInTime,getdate()) end as test
from myUser where DelFlag='0' and UserID='cq'
2:
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)' end as test
from myUser where DelFlag='0' and UserID='cq'
3:
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)'
else DATEDIFF(day,ComeInTime,getdate()) end as test
from myUser where DelFlag='0' and UserID='cq'
为什么1 和2 执行都没有问题,3 却报“将 varchar 值 ' 480(已离职)' 转换为数据类型为 int 的列时发生语法错误。”
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)'
else str(DATEDIFF(day,ComeInTime,getdate())) end as test
from myUser where DelFlag='0' and UserID='cq'
这样试一下.语法应该没有问题
我的语句:(SQL 2000)
1:
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime)) else DATEDIFF(day,ComeInTime,getdate()) end as test
from myUser where DelFlag='0' and UserID='cq'
2:
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)' end as test
from myUser where DelFlag='0' and UserID='cq'
3:
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)'
else DATEDIFF(day,ComeInTime,getdate()) end as test
from myUser where DelFlag='0' and UserID='cq'
为什么1 和2 执行都没有问题,3 却报“将 varchar 值 ' 480(已离职)' 转换为数据类型为 int 的列时发生语法错误。”
select ByeTime,case when len(ByeTime)>0 then str(DATEDIFF(day,ComeInTime,ByeTime))+'(已离职)'
else str(DATEDIFF(day,ComeInTime,getdate())) end as test
from myUser where DelFlag='0' and UserID='cq'
这样试一下.语法应该没有问题
3.
WHEN THEN
WHEN THEN
ELSE
END
-- ------------
select count( *) as ct , CASE
WHEN (Pro_state = 1) THEN ' 待审 '
WHEN (Pro_state = 2) THEN ' 已审 '
WHEN (Pro_state = 3) THEN ' 办理中 '
WHEN (Pro_state = 4) THEN ' 已申退 '
WHEN (Pro_state = 5) THEN ' 来信 '
WHEN (Pro_state = 6) THEN ' 办理完成 '
WHEN (Pro_state = 7) THEN ' 未予立案 '
end as pro_state
from vw_proposals_query where 1 = 1 GROUP BY pro_state
结果:
ct pro -state
30 办理中
7 来信
1 办理完成
5 未予立案
解读:
查询总数和pro_state 当 Pro_state = 1 时以 ' 待审 ' 替代表示
当 Pro_state = 2 时以 ' 已审 ' 替代表示
当 Pro_state = 3 时以 ' 办理中 ' 替代表示
。。。。。。
CASE 可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段,但是它还具有更多用法。例如,你可以在
WHERE 子句中使用
CASE。
首先让我们看一下 CASE 的语法。在一般的 SELECT 中,其语法如下:
SELECT <myColumnSpec > =
CASE
WHEN <A > THEN <somethingA >
WHEN <B > THEN <somethingB >
ELSE <somethingE >
END
在上面的代码中需要用具体的参数代替尖括号中的内容。下面是一个简单的例子:
USE pubs
GO
SELECT
Title,
' Price Range ' =
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END
FROM titles
ORDER BY price
GO
这是 CASE 的典型用法,但是使用 CASE 其实可以做更多的事情。比方说下面的 GROUP BY 子句中的 CASE:
SELECT ' Number of Titles ', Count( *)
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END
GO
你甚至还可以组合这些选项,添加一个 ORDER BY 子句,如下所示:
USE pubs
GO
SELECT
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END AS Range,
Title
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END,
Title
ORDER BY
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END,
Title
GO
注意,为了在 GROUP BY 块中使用 CASE,查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
除了选择自定义字段之外,在很多情况下 CASE 都非常有用。再深入一步,你还可以得到你以前认为不可能得到的分组排序结果集。
首先让我们看一下 CASE 的语法。在一般的 SELECT 中,其语法如下:
SELECT <myColumnSpec > =
CASE
WHEN <A > THEN <somethingA >
WHEN <B > THEN <somethingB >
ELSE <somethingE >
END
在上面的代码中需要用具体的参数代替尖括号中的内容。下面是一个简单的例子:
USE pubs
GO
SELECT
Title,
' Price Range ' =
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END
FROM titles
ORDER BY price
GO
这是 CASE 的典型用法,但是使用 CASE 其实可以做更多的事情。比方说下面的 GROUP BY 子句中的 CASE:
SELECT ' Number of Titles ', Count( *)
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END
GO
你甚至还可以组合这些选项,添加一个 ORDER BY 子句,如下所示:
USE pubs
GO
SELECT
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END AS Range,
Title
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END,
Title
ORDER BY
CASE
WHEN price IS NULL THEN ' Unpriced '
WHEN price < 10 THEN ' Bargain '
WHEN price BETWEEN 10 and 20 THEN ' Average '
ELSE ' Gift to impress relatives '
END,
Title
GO
注意,为了在 GROUP BY 块中使用 CASE,查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
除了选择自定义字段之外,在很多情况下 CASE 都非常有用。再深入一步,你还可以得到你以前认为不可能得到的分组排序结果集。