Case When 解决简单的"是"与"否"
昨天晚上买了一个sql的新书,特别的高兴,晚上就阅读了很多,突然发现以前经常在SQL中使用的case when的用法,以前在csdn上面看到好多,但是就是不知道怎么用,可能那个时候还没有用到的地方,不过现在就不一样了,看过那短短的3页纸之后,加强领悟了一个已经领悟的道理:书本还是要的,网络不可以解决所有的问题!
至于case when用在那里,我在这里只是用到了一小部分,一个是与否的问题;
如图所示:
![](/images/cnblogs_com/xucanzhao/casewhen.jpg)
以前的做法是在DataGrid_ItemDataBound事件里面处理,
大概的做法是这样写的
昨天看了case when的用法之后,才知道不要那么麻烦了
但是现在还不知道性能怎么样,因为我对测试性能是一无所知。
至于case when用在那里,我在这里只是用到了一小部分,一个是与否的问题;
如图所示:
![](/images/cnblogs_com/xucanzhao/casewhen.jpg)
以前的做法是在DataGrid_ItemDataBound事件里面处理,
大概的做法是这样写的
1
Private Sub DataGrid1_ItemDataBound(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles Datagrid1.ItemDataBound
2
If (e.Item.ItemIndex <> -1) Then
3
' e.Item.Cells(1).Text = System.Convert.ToString(e.Item.ItemIndex + 1 + DataGrid1.CurrentPageIndex * DataGrid1.PageSize)
4
If e.Item.Cells(5).Text.ToString = "False" Then
5
e.Item.Cells(5).Text = "没有通过"
6
e.Item.BackColor = Color.LightSkyBlue
7![](/Images/OutliningIndicators/InBlock.gif)
8
Else
9
If e.Item.Cells(5).Text = "True" Then
10
e.Item.Cells(5).Text = "已经通过"
11
End If
12
End If
13
End If
14
End Sub
超级麻烦,不过可以定义背景颜色,也很不错![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
2
![](/Images/OutliningIndicators/InBlock.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
昨天看了case when的用法之后,才知道不要那么麻烦了
1
Select
2
a.UserBorrowID,a.UserID,a.BookID,a.BorrowDate,b.UserName,c.Title ,a.GoOnDate,a.IsReturn,
3
a.IsGoOn,
4
a.ReturnDate,
5
case a.IsGoOn
6
when 1 then '是'
7
when 0 then '否'
8
END As IsGoOnBorrow,
9
case a.IsReturn
10
when 1 then '是'
11
when 0 then '否'
12
End as IsReturn1
13
from Library_Borrows as a,Library_books as c,Library_Users as b
14
where
15
a.bookid=c.bookid and a.userid=b.UserID and a.UserID=@UserID
16
ORDER BY a.ModifyDate DESC
^_^,建立一个存储过程,然后就不用再ItemBound里面写了。这样就可以解决问题,当然还可以拓展开来,比如可以用case when 的搜索形式进行变换字符串 case when GoOndate<... then '通过' 这样就可以减少在.vb中写代码了。![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
但是现在还不知道性能怎么样,因为我对测试性能是一无所知。