1.1w+
社区成员
- 主页
gerrycen进来
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
你仔细看看！
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
你仔细看看！
...全文
请发表友善的回复…
发表回复
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)
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
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
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 213，级别 16，状态 4，行 1
插入错误: 列名或所提供值的数目与表定义不匹配。
gerrycen 2003-01-19
加上这行试试
020000705290 13007215828 李锋 赣字第081817 朱咏刚 8501816－20615 南昌p11 15996 2003 2000-9-13 3
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')
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)
也是对的！
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到查询分析器！试试！它是对的！
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
020000704832 13006207062 吴国平 360103690912171 万里明 6252442 南昌p11 3000 1 2003 2000-9-5 3
pengdali 2003-01-19
你试试！
insert语句只有10个字段，而你的表有12个列
insert语句只有10个字段，而你的表有12个列
gerrycen 2003-01-19
不会吧