求各位帮忙解答这个问题

Question

tableCId state flag createtime updatetime deletetime 
1 创建 无 2011-07-07 2011-03-03 2011-03-04 
1 删除 无 2011-03-06 2011-03-07 2011-08-07 
1 更新 1 2011-04-03 2011-08-13 2011-04-04 
3 创建 无 2011-07-07 2011-03-03 2011-03-04 
3 删除 无 2011-03-06 2011-03-07 2011-08-07 
我想实现这样的效果
Id state Time(处理时间)
1 创建 2011-07-07
1 删除 2011-08-07
1 更新 2011-08-13
 
页面上有一个下拉框，根据下拉框查找数据<asp:dropdownlist id="DropDownList4" tabIndex="2" runat="server" CssClass="smallhtbox">
<asp:ListItem Value="1" Selected="True">创建</asp:ListItem>
<asp:ListItem Value="2">删除</asp:ListItem>
<asp:ListItem Value="3">更新</asp:ListItem>
</asp:dropdownlist>
比如搜索 创建 页面上的datagrid的Time列就显示 创建 所在的数据行的 createtime 列的数据，
比如搜索 删除 页面上的datagrid的Time列就显示 删除 所在的数据行的 deletetime 列的数据，
比如搜索 更新 页面上的datagrid的Time列就显示 更新 所在的数据行的 updatetime 列的数据请问这样一句语句应该怎么写？

Answer 1

create table tb(Id int,state nvarchar(10),flag nvarchar(10),createtime datetime,updatetime datetime,deletetime datetime)  
insert into tb select 1,'创建','无','2011-07-07','2011-03-03','2011-03-04'
insert into tb select 1,'删除','无','2011-03-06','2011-03-07','2011-08-07'
insert into tb select 1,'更新','1','2011-04-03','2011-08-13','2011-04-04'
insert into tb select 3,'创建','无','2011-07-07','2011-03-03','2011-03-04'
insert into tb select 3,'删除','无','2011-03-06','2011-03-07','2011-08-07'
go
id          state      dealtime
----------- ---------- -----------------------
1           创建         2011-07-07 00:00:00.000
1           删除         2011-08-07 00:00:00.000
1           更新         2011-08-13 00:00:00.000(3 行受影响)*/
go
drop table tb

Answer 2

if Object_id('tb') is not null
drop Table tb
go
create table tb(Id int,state nvarchar(10),flag nvarchar(10),createtime datetime,updatetime datetime,deletetime datetime)  
go
insert into tb 
select 1,'创建','无','2011-07-07','2011-03-03','2011-03-04' union all
select 1,'删除','无','2011-03-06','2011-03-07','2011-08-07' union all
select 1,'更新','1','2011-04-03','2011-08-13','2011-04-04'  union all
select 3,'创建','无','2011-07-07','2011-03-03','2011-03-04' union all
select 3,'删除','无','2011-03-06','2011-03-07','2011-08-07'select * from tbselect Id, state,
[Time] = case state
when '创建' then createtime
when '删除' then deletetime
when '更新' then updatetime
end
from tb
/*
Id          state      Time
----------- ---------- -----------------------
1           创建         2011-07-07 00:00:00.000
1           删除         2011-08-07 00:00:00.000
1           更新         2011-08-13 00:00:00.000
3           创建         2011-07-07 00:00:00.000
3           删除         2011-08-07 00:00:00.000
*/

Answer 3

create table tb(Id int,state nvarchar(10),flag nvarchar(10),createtime datetime,updatetime datetime,deletetime datetime)  
insert into tb select 1,'创建','无','2011-07-07','2011-03-03','2011-03-04'
insert into tb select 1,'删除','无','2011-03-06','2011-03-07','2011-08-07'
insert into tb select 1,'更新','1','2011-04-03','2011-08-13','2011-04-04'
insert into tb select 3,'创建','无','2011-07-07','2011-03-03','2011-03-04'
insert into tb select 3,'删除','无','2011-03-06','2011-03-07','2011-08-07'
go
select id,state,
(case when state='创建' then createtime when state='删除' then deletetime when state='更新' then updatetime end) as dealtime
from tb
where id=1
/*
id          state      dealtime
----------- ---------- -----------------------
1           创建         2011-07-07 00:00:00.000
1           删除         2011-08-07 00:00:00.000
1           更新         2011-08-13 00:00:00.000(3 行受影响)*/
go
drop table tb如果是用参数进行查询,可以考虑将参数传入后以参数来确定查哪一种,