gerrycen进来

pengdali 研发总监  2003-01-19 04:13:53
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

你仔细看看!
...全文
23 点赞 收藏 21
写回复
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
pengdali 2003-01-19
哈哈!88
回复
gerrycen 2003-01-19
那我有机会去广州出差,去看你!!!!!!!!!!!
谢谢!
回复
pengdali 2003-01-19
我在广州上班!哈哈!
回复
gerrycen 2003-01-19
谢谢你,你是在南昌上班吗,有机会我想见一见救命恩人!!!!!!
回复
pengdali 2003-01-19
update nc_p11_lost set on_time=DATEDIFF(month, online,getDate())
回复
pengdali 2003-01-19
update nc_p11_lost set on_time=DATEDIFF(month, online,'200212'+'01')
回复
pengdali 2003-01-19
我上面不是写了吗??
回复
gerrycen 2003-01-19
还有我想用200212-online=XX月放入on_time中,
回复
pengdali 2003-01-19
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)
回复
pengdali 2003-01-19
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
回复
gerrycen 2003-01-19
020000711629 13007215618 胡玮 赣消字第1645号 吴芳 3761442 南昌p11 9630 null 2003 2000-8-10 3 3
回复
pengdali 2003-01-19
又少了两列!
服务器: 消息 213,级别 16,状态 4,行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
回复
gerrycen 2003-01-19
加上这行试试
020000705290 13007215828 李锋 赣字第081817 朱咏刚 8501816-20615 南昌p11 15996 2003 2000-9-13 3
回复
gerrycen 2003-01-19
我里面还有其它证件可能为10位的,我只想计算15和18位的
回复
pengdali 2003-01-19
-----还有我想用200212-online=XX月放入on_time中

update nc_p11_lost set on_time=DATEDIFF(month, online,'2002-12-1')
回复
pengdali 2003-01-19
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 2003-01-19
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到查询分析器!试试!它是对的!
回复
gerrycen 2003-01-19
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 2003-01-19
你试试!
insert语句只有10个字段,而你的表有12个列
回复
gerrycen 2003-01-19
不会吧
回复
发动态
发帖子
非技术版
创建于2007-09-28

1.1w+

社区成员

MS-SQL Server 非技术版
申请成为版主
社区公告
暂无公告