use [knowfardbms]
GO

Alter TABLE [dbms_normal_special] ADD [分类号] [nvarchar](10) NULL DEFAULT('')
GO
UPDATE [dbms_normal_special] SET [分类号]='A' WHERE [Title]='测量与导航工程'
UPDATE [dbms_normal_special] SET [分类号]='AT2181' WHERE [Title]='卫星定位与导航应用'
UPDATE [dbms_normal_special] SET [分类号]='AT3121' WHERE [Title]='军事大地测量'
UPDATE [dbms_normal_special] SET [分类号]='AT3141' WHERE [Title]='军事海洋测量'

------UPDATE [dbms_normal_special] SET [分类号]='AT3165' WHERE [Title]='卫星导航定位与军用时频'
UPDATE [dbms_normal_special] SET [分类号]='AT3165' WHERE [Title]='军事时频技术'
GO

UPDATE [dbms_normal_special] SET [分类号]='AU31' WHERE [Title]='制导、导航与控制技术'
UPDATE [dbms_normal_special] SET [分类号]='AU65' WHERE [Title]='军事航天技术'
UPDATE [dbms_normal_special] SET [分类号]='B' WHERE [Title]='遥感信息工程'
UPDATE [dbms_normal_special] SET [分类号]='BT3161' WHERE [Title]='遥感'
UPDATE [dbms_normal_special] SET [分类号]='BT3131' WHERE [Title]='军事摄影测量'
UPDATE [dbms_normal_special] SET [分类号]='BU71' WHERE [Title]='军用遥感技术'
UPDATE [dbms_normal_special] SET [分类号]='C' WHERE [Title]='地图学与地理信息工程'

------UPDATE [dbms_normal_special] SET [分类号]='CT3171' WHERE [Title]='军事地图制图'
UPDATE [dbms_normal_special] SET [分类号]='CT3171' WHERE [Title]='军事地图与制图'
GO

UPDATE [dbms_normal_special] SET [分类号]='CT11' WHERE [Title]='军事地理'
UPDATE [dbms_normal_special] SET [分类号]='CT21' WHERE [Title]='军事地形'
UPDATE [dbms_normal_special] SET [分类号]='CT41' WHERE [Title]='军用地图'
UPDATE [dbms_normal_special] SET [分类号]='D' WHERE [Title]='作战环境与系统工程'
UPDATE [dbms_normal_special] SET [分类号]='DT00' WHERE [Title]='军事环境理论'
UPDATE [dbms_normal_special] SET [分类号]='DT81' WHERE [Title]='外层空间与军事'
UPDATE [dbms_normal_special] SET [分类号]='DT51' WHERE [Title]='军事气象'
UPDATE [dbms_normal_special] SET [分类号]='DT61' WHERE [Title]='军事水文'
UPDATE [dbms_normal_special] SET [分类号]='DT71' WHERE [Title]='军事海洋'
UPDATE [dbms_normal_special] SET [分类号]='DU1323' WHERE [Title]='虚拟现实技术'
UPDATE [dbms_normal_special] SET [分类号]='DU1331' WHERE [Title]='军事环境科学技术'
UPDATE [dbms_normal_special] SET [分类号]='E' WHERE [Title]='测量工程与装备'
UPDATE [dbms_normal_special] SET [分类号]='ET3151' WHERE [Title]='军事工程测量'
UPDATE [dbms_normal_special] SET [分类号]='ET3191' WHERE [Title]='军事测绘装备'
UPDATE [dbms_normal_special] SET [分类号]='ET3181' WHERE [Title]='军事测绘保障'
UPDATE [dbms_normal_special] SET [分类号]='F' WHERE [Title]='密码装备与工程'
UPDATE [dbms_normal_special] SET [分类号]='FX3191' WHERE [Title]='密码技术'
UPDATE [dbms_normal_special] SET [分类号]='FJ5131' WHERE [Title]='机要工作保障'
UPDATE [dbms_normal_special] SET [分类号]='FJ5591' WHERE [Title]='保密设施'
UPDATE [dbms_normal_special] SET [分类号]='FU111' WHERE [Title]='军用数学'
UPDATE [dbms_normal_special] SET [分类号]='G' WHERE [Title]='军用通信工程'
UPDATE [dbms_normal_special] SET [分类号]='GX11' WHERE [Title]='通信技术'
UPDATE [dbms_normal_special] SET [分类号]='GX21' WHERE [Title]='通信联络组织与通信保障'
UPDATE [dbms_normal_special] SET [分类号]='GX25' WHERE [Title]='通信手段、方式'

------UPDATE [dbms_normal_special] SET [分类号]='GX3171' WHERE [Title]='通信安全'
------UPDATE [dbms_normal_special] SET [分类号]='GX3181' WHERE [Title]='通信保密'
UPDATE [dbms_normal_special] SET [分类号]='GX3181' WHERE [Title]='通信安全与保密'
GO
UPDATE [dbms_normal_special] SET [分类号]='H' WHERE [Title]='信息安全与技术'
------UPDATE [dbms_normal_special] SET [分类号]='HJ5571' WHERE [Title]='信息安全保密'
UPDATE [dbms_normal_special] SET [分类号]='HJ5571' WHERE [Title]='信息安全与保密'
GO

------UPDATE [dbms_normal_special] SET [分类号]='HJ5581' WHERE [Title]='保密防护技术'
UPDATE [dbms_normal_special] SET [分类号]='HJ5581' WHERE [Title]='保密设备与防护技术'
GO

------UPDATE [dbms_normal_special] SET [分类号]='HU1319' WHERE [Title]='电子技术'
UPDATE [dbms_normal_special] SET [分类号]='HU1319' WHERE [Title]='电子战与技术'
GO

UPDATE [dbms_normal_special] SET [分类号]='HU2121' WHERE [Title]='信息技术'
UPDATE [dbms_normal_special] SET [分类号]='HU79' WHERE [Title]='网络对抗技术'
UPDATE [dbms_normal_special] SET [分类号]='I' WHERE [Title]='军事装备与作战指挥'
UPDATE [dbms_normal_special] SET [分类号]='IX3125' WHERE [Title]='通信装备、器材管理'
UPDATE [dbms_normal_special] SET [分类号]='IX91' WHERE [Title]='通信装备与器材'
UPDATE [dbms_normal_special] SET [分类号]='IG31' WHERE [Title]='作战指挥'
UPDATE [dbms_normal_special] SET [分类号]='J' WHERE [Title]='军事情报学'
UPDATE [dbms_normal_special] SET [分类号]='JJ15' WHERE [Title]='情报与侦察'

------UPDATE [dbms_normal_special] SET [分类号]='JU33' WHERE [Title]='军事探测、侦察、跟踪、监视技术'
UPDATE [dbms_normal_special] SET [分类号]='JU33' WHERE [Title]='军事探测与监视技术'
GO
UPDATE [dbms_normal_special] SET [分类号]='JU33' WHERE [Title]='探测与监视技术'
GO

Alter TABLE [dbms_module_article] ADD [分类名] [nvarchar](100) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_article] ADD [分类号] [nvarchar](10) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_hylw] ADD [分类名] [nvarchar](100) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_hylw] ADD [分类号] [nvarchar](10) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_qklw] ADD [分类名] [nvarchar](100) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_qklw] ADD [分类号] [nvarchar](10) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_xwlw] ADD [分类名] [nvarchar](100) NULL DEFAULT('')
GO
Alter TABLE [dbms_module_xwlw] ADD [分类号] [nvarchar](10) NULL DEFAULT('')
GO

Alter TABLE [dbms_module_article] ADD [知远专题编号] [int] NOT NULL DEFAULT(0)
GO
Alter TABLE [dbms_module_hylw] ADD [知远专题编号] [int] NOT NULL DEFAULT(0)
GO
Alter TABLE [dbms_module_qklw] ADD [知远专题编号] [int] NOT NULL DEFAULT(0)
GO
Alter TABLE [dbms_module_xwlw] ADD [知远专题编号] [int] NOT NULL DEFAULT(0)
GO

UPDATE [dbms_module_article] set [知远专题编号]=(
 select sid from [dbms_normal_specialcontent]
 where contentid=[dbms_module_article].id and channelid=[dbms_module_article].channelid
)
GO
UPDATE [dbms_module_article] set [分类名]=(
 select [Title] from [dbms_normal_special]
 where id=[dbms_module_article].知远专题编号
)
GO
UPDATE [dbms_module_article] set [分类号]=(
 select [分类号] from [dbms_normal_special]
 where id=[dbms_module_article].知远专题编号
)
GO

UPDATE [dbms_module_hylw] set [知远专题编号]=(
 select sid from [dbms_normal_specialcontent]
 where contentid=[dbms_module_hylw].id and channelid=[dbms_module_hylw].channelid
)
GO
UPDATE [dbms_module_hylw] set [分类名]=(
 select [Title] from [dbms_normal_special]
 where id=[dbms_module_hylw].知远专题编号
)
GO
UPDATE [dbms_module_hylw] set [分类号]=(
 select [分类号] from [dbms_normal_special]
 where id=[dbms_module_hylw].知远专题编号
)
GO

UPDATE [dbms_module_qklw] set [知远专题编号]=(
 select sid from [dbms_normal_specialcontent]
 where contentid=[dbms_module_qklw].id and channelid=[dbms_module_qklw].channelid
)
GO
UPDATE [dbms_module_qklw] set [分类名]=(
 select [Title] from [dbms_normal_special]
 where id=[dbms_module_qklw].知远专题编号
)
GO
UPDATE [dbms_module_qklw] set [分类号]=(
 select [分类号] from [dbms_normal_special]
 where id=[dbms_module_qklw].知远专题编号
)
GO

UPDATE [dbms_module_xwlw] set [知远专题编号]=(
 select sid from [dbms_normal_specialcontent]
 where contentid=[dbms_module_xwlw].id and channelid=[dbms_module_xwlw].channelid
)
GO
UPDATE [dbms_module_xwlw] set [分类名]=(
 select [Title] from [dbms_normal_special]
 where id=[dbms_module_xwlw].知远专题编号
)
GO
UPDATE [dbms_module_xwlw] set [分类号]=(
 select [分类号] from [dbms_normal_special]
 where id=[dbms_module_xwlw].知远专题编号
)
GO

Alter TABLE [dbms_module_article] ADD [语言] [nvarchar](4) NULL DEFAULT('')
GO
UPDATE [dbms_module_article] SET [语言]='外文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=1)
GO
UPDATE [dbms_module_article] SET [语言]='中文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=2)
GO
Alter TABLE [dbms_module_hylw] ADD [语言] [nvarchar](4) NULL DEFAULT('')
GO
UPDATE [dbms_module_hylw] SET [语言]='外文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=1)
GO
UPDATE [dbms_module_hylw] SET [语言]='中文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=2)
GO
Alter TABLE [dbms_module_qklw] ADD [语言] [nvarchar](4) NULL DEFAULT('')
GO
UPDATE [dbms_module_qklw] SET [语言]='外文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=1)
GO
UPDATE [dbms_module_qklw] SET [语言]='中文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=2)
GO
Alter TABLE [dbms_module_xwlw] ADD [语言] [nvarchar](4) NULL DEFAULT('')
GO
UPDATE [dbms_module_xwlw] SET [语言]='外文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=1)
GO
UPDATE [dbms_module_xwlw] SET [语言]='中文' WHERE [知远专题编号] in (SELECT id FROM [dbms_normal_special] WHERE [GroupID]=2)
GO

DROP table [dbms_module_ebook]
GO

DROP table [dbms_module_file]
GO

DROP table [dbms_module_gjb]
GO

DROP table [dbms_module_rules]
GO

DROP table [dbms_normal_admin]
GO

DROP table [dbms_normal_adminlogs]
GO

DROP table [dbms_normal_category]
GO

DROP table [dbms_normal_channel]
GO

DROP table [dbms_normal_class]
GO

DROP table [dbms_normal_database]
GO

DROP table [dbms_normal_databaseindex]
GO

DROP table [dbms_normal_importlogs]
GO

DROP table [dbms_normal_module]
GO

DROP table [dbms_normal_qikan]
GO

DROP table [dbms_normal_qikancontent]
GO

DROP table [dbms_normal_specialcontent]
GO

DROP table [dbms_normal_system]
GO

DROP table [dbms_normal_tag]
GO

EXEC sp_rename 'dbms_module_article', '普通文章'
GO

EXEC sp_rename 'dbms_module_xwlw', '学位论文'
GO

EXEC sp_rename 'dbms_module_qklw', '期刊论文'
GO

EXEC sp_rename 'dbms_module_hylw', '会议论文'
GO

EXEC sp_rename 'dbms_normal_special', '知远数据库分类'
GO

declare   @s   varchar(8000)
set   @s= ''
select   @s=@s+ '
alter table ['+b.name+ '] drop constraint ['+d.name+ '] '
from   syscolumns   a
join   sysobjects   b   on   a.id=b.id
join   syscomments   c   on   a.cdefault=c.id
join   sysobjects   d   on   c.id=d.id
where (b.name= '普通文章' OR b.name= '期刊论文' OR b.name= '会议论文' OR b.name= '学位论文')
and   (a.name= 'ChannelId' OR a.name= 'ClassId' OR a.name= 'JunFenHao' OR a.name= 'ZhongTuHao')
exec(@s)
GO
Alter table [普通文章] Drop Column [ChannelId]
Alter table [普通文章] Drop Column [ClassId]
Alter table [普通文章] Drop Column [JunFenHao]
Alter table [普通文章] Drop Column [ZhongTuHao]

Alter table [会议论文] Drop Column [ChannelId]
Alter table [会议论文] Drop Column [ClassId]
Alter table [会议论文] Drop Column [JunFenHao]
Alter table [会议论文] Drop Column [ZhongTuHao]

Alter table [期刊论文] Drop Column [ChannelId]
Alter table [期刊论文] Drop Column [ClassId]
Alter table [期刊论文] Drop Column [JunFenHao]
Alter table [期刊论文] Drop Column [ZhongTuHao]

Alter table [学位论文] Drop Column [ChannelId]
Alter table [学位论文] Drop Column [ClassId]
Alter table [学位论文] Drop Column [JunFenHao]
Alter table [学位论文] Drop Column [ZhongTuHao]

SELECT count([Id]) as 普通文章未归类 FROM [普通文章] Where [分类号] is null
SELECT count([Id]) as 学位论文未归类 FROM [学位论文] Where [分类号] is null
SELECT count([Id]) as 期刊论文未归类 FROM [期刊论文] Where [分类号] is null
SELECT count([Id]) as 会议论文未归类 FROM [会议论文] Where [分类号] is null