我有一个表:tb 如下: xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-10
北京 2009-09-08 2009-09-11
直销销售 2009-09-07 2009-09-12
直销处 2009-09-06 2009-09-13我的问题是:当xianshou字段的值包含:“直销”这个字的时候,facheriqi字段的值等于rukuriqi字段的值,结果如下: xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-09
北京 2009-09-08 2009-09-11
直销销售 2009-09-07 2009-09-07
直销处 2009-09-06 2009-09-06
直销 2009-09-09 2009-09-10
北京 2009-09-08 2009-09-11
直销销售 2009-09-07 2009-09-12
直销处 2009-09-06 2009-09-13我的问题是:当xianshou字段的值包含:“直销”这个字的时候,facheriqi字段的值等于rukuriqi字段的值,结果如下: xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-09
北京 2009-09-08 2009-09-11
直销销售 2009-09-07 2009-09-07
直销处 2009-09-06 2009-09-06
select xiaoshou, rukuriqi ,
facheriqi=case when patindex('%直销%', facheriqi) then rukuriqi else xiaoshou end
from tb
update tb
set facheriqi=
case when charindex('直销',xiaoshou)>0 then rukuriqi else facheriqi end
这个意思?
select xiaoshou,rukuriqi,(case when rukuriqi=facheriqi then rukuriqi else facheriqi end ) facheriqi from tb where xiaoshou like '%直销%'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](xiaoshou varchar(12),rukuriqi datetime,facheriqi datetime)
insert [tb] select
'直销', '2009-09-09' , '2009-09-10' union all select
'北京' , '2009-09-08' , '2009-09-11' union all select
'直销销售', '2009-09-07', '2009-09-12' union all select
'直销处' , '2009-09-06', '2009-09-13' update tb
set facheriqi=
case when charindex('直销',xiaoshou)>0 then rukuriqi else facheriqi end---------------------------
select * from tbxiaoshou rukuriqi facheriqi
------------ ----------------------- -----------------------
直销 2009-09-09 00:00:00.000 2009-09-09 00:00:00.000
北京 2009-09-08 00:00:00.000 2009-09-11 00:00:00.000
直销销售 2009-09-07 00:00:00.000 2009-09-07 00:00:00.000
直销处 2009-09-06 00:00:00.000 2009-09-06 00:00:00.000(4 行受影响)
go
create table tb(xiaoshou nvarchar(10), rukuriqi nvarchar(10), facheriqi nvarchar(10))
go
insert into tb
select
'直销', '2009-09-09' , '2009-09-10' union all select
'北京' , '2009-09-08' , '2009-09-11' union all select
'直销销售', '2009-09-07', '2009-09-12' union all select
'直销处' , '2009-09-06', '2009-09-13'
select xiaoshou, rukuriqi ,
facheriqi=case when patindex('%直销%', facheriqi)>0 then facheriqi else rukuriqi end
from tb/*
xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-09
北京 2009-09-08 2009-09-08
直销销售 2009-09-07 2009-09-07
直销处 2009-09-06 2009-09-06
*/
go
create table tb(xiaoshou nvarchar(10), rukuriqi nvarchar(10), facheriqi nvarchar(10))
go
insert into tb
select
'直销', '2009-09-09' , '2009-09-10' union all select
'北京' , '2009-09-11' , '2009-09-11' union all select
'直销销售', '2009-09-07', '2009-09-12' union all select
'直销处' , '2009-09-06', '2009-09-13'
select xiaoshou, rukuriqi ,
facheriqi=case when patindex('%直销%', facheriqi)>0 then facheriqi else rukuriqi end
from tb/*
xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-09
北京 2009-09-11 2009-09-11
直销销售 2009-09-07 2009-09-07
直销处 2009-09-06 2009-09-06
*/刚才数据选择错了!
update
tb
set
facheriqi=case when charindex('直销',xiaoshou)>0 then rukuriqi else facheriqi end
declare @tb table([xiaoshou] varchar(8),[rukuriqi] datetime,[facheriqi] datetime)
insert @tb
select '直销','2009-09-09','2009-09-10' union all
select '北京','2009-09-08','2009-09-11' union all
select '直销销售','2009-09-07','2009-09-12' union all
select '直销处','2009-09-06','2009-09-13'select [xiaoshou], [rukuriqi],facheriqi=case when charindex('直销',[xiaoshou])>0 then rukuriqi else facheriqi end
from @tb
/*
xiaoshou rukuriqi facheriqi
-------- ----------------------- -----------------------
直销 2009-09-09 00:00:00.000 2009-09-09 00:00:00.000
北京 2009-09-08 00:00:00.000 2009-09-11 00:00:00.000
直销销售 2009-09-07 00:00:00.000 2009-09-07 00:00:00.000
直销处 2009-09-06 00:00:00.000 2009-09-06 00:00:00.000(4 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-21 16:20:20
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([xiaoshou] varchar(8),[rukuriqi] datetime,[facheriqi] datetime)
insert [tb]
select '直销','2009-09-09','2009-09-10' union all
select '北京','2009-09-08','2009-09-11' union all
select '直销销售','2009-09-07','2009-09-12' union all
select '直销处','2009-09-06','2009-09-13'
--------------开始查询--------------------------
update
tb
set
facheriqi=case when charindex('直销',xiaoshou)>0 then rukuriqi else facheriqi endselect * from tb
----------------结果----------------------------
/* xiaoshou rukuriqi facheriqi
-------- ----------------------- -----------------------
直销 2009-09-09 00:00:00.000 2009-09-09 00:00:00.000
北京 2009-09-08 00:00:00.000 2009-09-11 00:00:00.000
直销销售 2009-09-07 00:00:00.000 2009-09-07 00:00:00.000
直销处 2009-09-06 00:00:00.000 2009-09-06 00:00:00.000(4 行受影响)*/
xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-10
北京 2009-09-08 2009-09-11
直销销售 2009-09-07 2009-09-12
直销处 2009-09-06 2009-09-13
销售 2009-09-05 2009-09-14 我的问题是:当xianshou字段的值包含:“直销”或者“销售”这个字的时候,facheriqi字段的值等于rukuriqi字段的值,结果如下: xiaoshou rukuriqi facheriqi
直销 2009-09-09 2009-09-09
北京 2009-09-08 2009-09-11
直销销售 2009-09-07 2009-09-07
直销处 2009-09-06 2009-09-06
销售 2009-09-05 2009-09-05