gerrycen进来

[pengdali]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nc_p11_lost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[nc_p11_lost]
GO

CREATE TABLE [dbo].[nc_p11_lost] (
[service_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[access_number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[customer_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[certificate_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[person] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[phone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[favourmenu_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fee] [int] NULL ,
[certificate_type] [int] NULL ,
[age] [int] NULL ,
[online] [datetime] NULL ,
[on_time] [int] NULL
) ON [PRIMARY]
GO

020000894086 13077903011 李娜 360102198011104881 南昌p11 27875 2 23 2001-9-26 2
020000896281 13077916788 张纯如 360103660314341 6823989 南昌p11 3100 1 2003 2001-9-30 2
020000905095 13097209229 王春红 360122810106122 南昌p11 16644 1 2003 2001-10-12 2
020000906099 13037207954 熊艳 360124198307140052 5661377 南昌p11 23345 2 20 2001-10-15 2
020000924646 13065188338 吴丽丽 360121820514292 8623558 南昌p11 16408 1 2003 2001-11-14 2
020000933355 13197916708 熊典迪 362226197001111512 6516694 南昌p11 3000 2 33 2001-11-24 2
020000894109 13077903079 赵蓉 360102197908131222 赵蓉 6210347 南昌p11 18140 2 24 2001-9-26 2
020000902557 13077901371 熊涂华 360000000011111 南昌p11 13504 1 2003 2001-10-9 2
020000955128 13097288971 唐玉红 360425530816581 8218541 南昌p11 3000 1 2003 2001-12-3 2
020000980563 13077953218 刘芳 362228721214004 8607189 南昌p11 3000 1 2003 2002-1-9 1

你仔细看看！

[gerrycen]

不会吧

[pengdali]

你试试！
insert语句只有10个字段，而你的表有12个列

[gerrycen]

020001963601 13077952808 熊昀 360122197711040023 熊昀 07916622031 南昌p11 8829 2 26 2002-8-4 12:23:23 1
020000704832 13006207062 吴国平 360103690912171 万里明 6252442 南昌p11 3000 1 2003 2000-9-5 3

[pengdali]

update nc_p11_lost
set age = year(getdate())-(case certificate_type
when 2 then cast(substring(certificate_id,7,4) as int) else 0 end)

---Ctrl+V到查询分析器！试试！它是对的！

[pengdali]

update nc_p11_lost
set age=year(getDate())-(case certificate_type
when 1 then cast(('19'+ cast(substring(certificate_id,7,2) as varchar)) as int) else
cast(substring(certificate_id,7,4) as int) end)

也是对的！

[pengdali]

-----还有我想用200212-online=XX月放入on_time中

update nc_p11_lost set on_time=DATEDIFF(month, online,'2002-12-1')

[gerrycen]

我里面还有其它证件可能为10位的，我只想计算15和18位的

[gerrycen]

加上这行试试
020000705290 13007215828 李锋 赣字第081817 朱咏刚 8501816－20615 南昌p11 15996 2003 2000-9-13 3

[pengdali]

又少了两列！
服务器: 消息 213，级别 16，状态 4，行 1
插入错误: 列名或所提供值的数目与表定义不匹配。

[gerrycen]

020000711629 13007215618 胡玮 赣消字第1645号 吴芳 3761442 南昌p11 9630 null 2003 2000-8-10 3 3

[pengdali]

update nc_p11_lost
set age=year(getDate())-(case certificate_type
when 1 then cast(('19'+ cast(substring(certificate_id,7,2) as varchar)) as int) else
cast(substring(certificate_id,7,4) as int) end) where certificate_type is not nul

[pengdali]

update nc_p11_lost
set age=year(getDate())-(case certificate_type
when 1 then cast(('19'+ cast(substring(certificate_id,7,2) as varchar)) as int) else
cast(substring(certificate_id,7,4) as int) end) where len(certificate_id) in (15,18)

[gerrycen]

还有我想用200212-online=XX月放入on_time中，

[pengdali]

我上面不是写了吗？？

[pengdali]

update nc_p11_lost set on_time=DATEDIFF(month, online,'200212'+'01')

[pengdali]

update nc_p11_lost set on_time=DATEDIFF(month, online,getDate())

[gerrycen]

谢谢你，你是在南昌上班吗，有机会我想见一见救命恩人！！！！！！

[pengdali]

我在广州上班！哈哈！

[gerrycen]

那我有机会去广州出差，去看你！！！！！！！！！！！
谢谢！

[pengdali]

哈哈！88