[原创]如何整合大量数据(世界级数据库)

这里只有作者精心编写的研究经历!
回复
头像
hellohappy
网站管理员
网站管理员
帖子: 330
注册时间: 2018年11月18日, 14:27
Has thanked: 1 time
Been thanked: 8 time

#1 [原创]如何整合大量数据(世界级数据库)

未读文章 hellohappy » 2018年12月29日, 22:25

如何整合大量数据
    这里以整合三个世界经济数据库为例。通过数据库软件整合数据库。但其实excel里面的 Power Query 已经可以完成大部分工作了,只是对于过大的数据,貌似会卡死或者内存不足。
    我写的很详细了,当然可能对大部分人来说都用不上。但当时如果没做记录,我估计也不记得当时收集数据的过程了,希望对有需要的人有帮助。当然,我还是建议你欣赏一下帖子,然后直接把数据取走即可。
(该系列还有一半内容在另一个帖子,但由于文章尚未发表,那部分文章涉及的部分暂时不公布)

第一帖:
    数据来源:
        前言:
           
你可以直接在百度云下载上面提到的数据:
        选择合适的数据来源导入:
                数据库1,IMFfiscalruledatabase:
                数据库2,WEOdatabases:
                数据库3,worldbankdatabase:
        依次查看各个数据来源的变量,确定数据库的关键字要包含哪些:
                数据库1,IMFfiscalruledatabase:
                数据库2,WEOdatabases:
                数据库3,worldbankdatabase:
                由前面三个数据库的字段进行总结,我们取所有字段的交集,得到最终我们要的数据库的大表!

    创建数据表供vbs导入
        新建一个数据库
        新建一个登录名
        赋数据库的访问权限予我们的登录名
        创建数据库对应的数据表


第二贴:
    编写vbs程序,将Excel导入数据库
        一个关于拖动鼠标就可以执行的vbs模版程序
        用vbs连接sql server 数据库的方法
        vbs链接数据库以后执行sql语句模版
        vbs定义Excle对象、工作薄对象、工作表对象模版
        读取数据库1的Excel内容,导入数据库
        数据库1的全部代码
        读取数据库2的Excel内容,导入数据库
        数据库2的全部代码
        读取数据库3的Excel内容,导入数据库
        数据库3的全部代码

    合并三张数据表
        根据三张数据表生成一张包含所有唯一国家和唯一时间的数据表
            首先我们看一下导入数据库以后的数据长什么样
            为了安全起见,当然是建议你操作之前备份一下这3张表
            一些操作sql的tips:
            确定合成的表的样子:
            总结一下前面为了合并三张数据表,而生成唯一id表的所有代码:
        各个数据表生成自己的视图
            数据库1生成自己的视图:

第三贴:
        数据库1的视图生成
        数据库2的视图生成代码
        数据库3的视图生成代码
            完成上面这么多步骤,你应该有以下这么多数据表:
        合并三个数据表的视图
            合并第三个数据库成5个视图:
            合并三个数据库成1个视图:

        输出数据表到Excel
        对excel表略加整理
           本帖和上一贴相关附件



数据来源:

    前言:
        这个主题的内容,需要你知道sql的基础知识(可以不会写,但是要会看,还需要一点点vbs编程的知识),整个数据库整合下来,十分烧电脑,也十分烧时间!不得不说,简单的关系数据库真的不适合处理超大数据量的高维数据,生成视图功能缺陷有些严重。(不排除是我代码写的烂,但是这已经不重要了,2333)
        对于一些量不大的数据,我们直接用Excel就可以整合了,但是对于很大的数据量,比如你想收集中国城市的数据,这里可能包含很多个数据库里面的很多数据,数据甚至是很多维度的。有时间维度,个体维度,个体的特征维度,变量维度,变量的特征维度等等。(如果不理解什么是特征维度,比如个体是城市,用城市的名字来表示个体,那么有可能你收集的数据还分了 市区 和 地区 ,或者直接不分市区和地区;)
        如此,你就需要自己去思考如何合并才能让数据不丢失(保证数据的完整性),但是又保持数据的简洁不冗余!
        这次,我收集的是世界级别的数据库!数据至少包含以下几个重要的维度:
            1.个体维度:国家名字
            2.时间维度:年份
            3.变量名字维度:变量名
            4.变量的各种附加信息维度:变量名对应的附加信息

        我是如何判断数据这些维度应该如何安排,如何录入才能最简化数据库呢?第一步当然是查看你的数据包含哪些信息。
        这次的世界级别的数据分别来源于
            1.IMF (国际货币基金组织)的IMF Fiscal Affairs Department部门的Fiscal Rules Dataset(1985 - 2015)数据库。后文简称IMFfiscalruledatabase
                网址:https://www.imf.org/external/datamapper ... erence.htm
            2.IMF (国际货币基金组织)的World Economic Outlook Database(2018年10月版)数据库。后文简称WEOdatabases
                网址:https://www.imf.org/external/pubs/ft/we ... index.aspx
            3.worldbank (世界银行)的World Development Indicators的数据库。后文简称worldbankdatabase
                网址:https://datacatalog.worldbank.org/datas ... indicators
                如果你只需要查找下载某个变量,你应该直接访问这个网页:
                https://data.worldbank.org.cn/indicator

        你可以直接在百度云下载上面提到的数据:
            数据库1,IMFfiscalruledatabase提取码:xpr9
            数据库2,WEOdatabases提取码:kp99
            数据库3,worldbankdatabase提取码:2a5y

    选择合适的数据来源导入:
        我们依次查看各个数据,选择比较好录入的文件格式进行整合。

            数据库1,IMFfiscalruledatabase:
                数据1里面包含了5个文件(网址是我自己加进去的),依次是数据库的Excel版,stata版,文字说明版,网址和附加信息。
IMFfiscalruledatabase的数据包括什么.png
IMFfiscalruledatabase的数据包括什么.png (24.25 KiB) 查看 855 次
IMFfiscalruledatabase的数据包括什么.png
IMFfiscalruledatabase的数据包括什么.png (24.25 KiB) 查看 855 次

                查看Excel版本数据:
IMFfiscalruledatabase的excel版.png

                查看Excel版本,发现数据还算整齐,标准的行列标题都有,行列标题共同对应一个数据。所以数据还是可以导入的,但是行列标题一般都不只是一个维度的,比如列标题Type of fiscal rule in place 就包含4个小的列标题,这样我们就需要额外的通过表格读取合并这两个大小标题,才能做到数据不丢失,不错乱。
                查看stata版本:
IMFfiscalruledatabase的stata版.png

                stata版本的数据简直是直接可以导入数据库,几乎不用自己去识别变量,也不需要自己重命名变量,因为他本身就是以变量名作为列标题的,我们只需要把他的变量名和变量名注释连同整个数据表一起复制出来就可以轻松导入数据库了。
                因此,我们确定了数据库IMFfiscalruledatabase的导入方法是直接同stata版本导入。

            数据库2,WEOdatabases
                数据2里面包含了2个文件夹:
数据库2包含1.png
数据库2包含1.png (11.76 KiB) 查看 850 次
数据库2包含1.png
数据库2包含1.png (11.76 KiB) 查看 850 次

                其中sdmx格式文件夹里面包含了一个sdmx格式(他应该是一种数据库的格式,类似于mysql的文件,我没有打开来看过,但是你如果感兴趣,你可以去搜索sdmx的官网,域名就是sdmx.org)的文件:
数据库2包含2.png
数据库2包含2.png (51.28 KiB) 查看 850 次
数据库2包含2.png
数据库2包含2.png (51.28 KiB) 查看 850 次

                另一个是制表符分割的文件夹,包含一个国家的WEO数据 WEOOct2018all.xls 和一个地区的WEO数据 WEOOct2018alla.xls:
数据库2包含3.png

                首先我们不太有精力再去学多一个sdmx的数据库软件,虽然他可能会帮助我们直接导入数据库而不用处理Excel,而我研究的对象是国家,所以确定了数据库WEOdatabases 的导入方法是通过Excel文件 WEOOct2018all.xls 导入

            数据库3,worldbankdatabase
                数据3里面包含了3个文件,依次是worldbank的数据,数据变动说明和网址:
数据库3包含.png
数据库3包含.png (14.81 KiB) 查看 850 次
数据库3包含.png
数据库3包含.png (14.81 KiB) 查看 850 次

                显然,没得选,我们确定了数据库worldbankdatabase 的导入方法是通过Excel文件 WDIEXCEL.xlsx 导入

    依次查看各个数据来源的变量,确定数据库的关键字要包含哪些:

            数据库1,IMFfiscalruledatabase:
                数据1是stata文件,他展示的数据时,本身就要求以二维面板形式展示,所以比较容易看出来他需要哪些关键字。如下图:

数据库1的变量名等信息.png

                上面说明,要完全包含全部信息,应该包含以下数据关系:
                    1.年份:year
                    2.国家已经对应国家信息:国家名,国家编号
                    3.变量名和变量注释:各种变量名,stata的变量注释信息
                某一个年份的某一个国家的某一个变量,可以唯一确定一个数据。
                从而如果我们使用简单的关系型数据库(其实多维数据库是最适合经济学研究的!因为我们的数据通常是多维的,而且很多是时候都是接近平衡面板,多维数据库有利于快速检索这类数据,直接用普通结构的数据库查询效率,数据库的占用空间都十分的不理想),而且不考率数据的存储容量(优化)问题。我们可以创建一个数据表包含如下字段,就可以把整个数据库包含进来。字段包括:国家名字、时间、变量名、变量别名、数据的值。但是,如果这样设定一张表,就会导致数据的值这个字段的内容,包含各种内容,比如包含国家的编号。所以,我们应该把非真正意义上的自变量的值,拿出来,一起丢到字段那边,以保证数据的值这个变量里面只包含真正意义上的自变量的值。从而字段应该包括:国家、国家编号、年份、变量名、变量别名、数据的值。
                由于我之前只学了怎么把Excel的数据导入到 SQL server 数据库的方法,所以我需要把这个数据库复制到Excel,再用vbs程序,把数据导入数据库。
                导出到excel的方法很多,比如 export excel using "C:\Users\Administrator\Desktop\Excelname.xls", firstrow(var) 或者你全选再粘贴到Excel中。但是通过这种方法导出到Excel你会发现一个问题,标签或者说变量的注释信息没办法一起导出来,他导出来的样子就是我们看到的stata展示数据的样子。
                所以我们就需要借助一些命令来导出label标签。这里我用的命令是stata的 svvarlbl 命令,这个命令在网上有的下载,你可以使用命令: search svvarlbl, all  ;弹出如下窗口:
stata安装命令举例.png
stata安装命令举例.png (23.59 KiB) 查看 848 次
stata安装命令举例.png
stata安装命令举例.png (23.59 KiB) 查看 848 次

                点击安装即可:
stata安装svvarlbl命令.png
stata安装svvarlbl命令.png (24.7 KiB) 查看 848 次
stata安装svvarlbl命令.png
stata安装svvarlbl命令.png (24.7 KiB) 查看 848 次

                当然你也可以直接去下载他的ado文件和hlp文件手动安装命令(这个方法通常适用于某些直接search不到的命令,原因是网络不通或者人家没有放上stata相关网站),后者不是必须的,后者只是为了方便你查看帮助信息。我这里直接上传在下面,其中,解压以后,需要自己复制到自己的ado文件夹下的s目录(因为这个命令是s开头的所以是s目录),如下:
安装svvarlbl命令.png
svvarlbl.zip
(1.56 KiB)
svvarlbl.zip
(1.56 KiB) 尚未被下载

                一般安装完成就可以直接使用了,比如,我这里输出标签命令: 
                   svvarlbl using "C:\Users\Administrator\Desktop\varlabel.log"
               得到如下log文件(如果你的log文件没有一个变量成一行,有可能是因为你给stata输出结果的窗口太窄了,他就自己换行还加了个>符号):
varlabel.log
(7.06 KiB)
varlabel.log
(7.06 KiB) 尚未被下载
               把文件中重复的部分用 ctrl+h 全部替换成空,剩下的就是变量的标签了,然后复制到Excel中转置,刚好就是原来的变量的顺序。
把变量标签取出来.png
重复的地方替换成空.png

               替换完成一行复制到Excel里面,再次以转置的方法粘贴,就得到了和原来变量次序一样的标签名字了。然后把结果复制到原来输出的Excelname.xls表里面。
获得变量的标签.png

               当然还有另外一种输出标签的方法(两个方法都是参考经管之家的),命令:search fsum, all; 然后安装fsum,在运行命令:fsum, stats(n) label ;得到如下的结果图:
stata的fsum命令获取标签名结果.png

               选中结果的变量和标签,右键Copy Table,然后在Excel中粘贴,同样需要转置,以复制到原来的Excelname.xls文件中。
               最终,我们得到了包含标签名字的数据库1的Excel版本的数据:Excelname.xls
               但是,这个xls在录入时候发现他还有一个问题,国家名字里面包含了单引号,比如Cote d'Ivoire 这个国家,但是去掉影响不大,所以需要后面自己替换掉所有的单引号。其他字符比如“&”、“\”、“--” 都没有。
Excelname.xls
(871 KiB)
Excelname.xls
(871 KiB) 尚未被下载


            数据库2,WEOdatabases
                数据2里面只要看 WEOOct2018all.xls 文件即可,我们先瞄一眼,看看他的数据结构是怎么样的。
数据库2的数据结构.png

                数据库2的数据由于是十分整齐的结构,所以不需要像前面一样自己去转换数据,调整数据次序什么的。我们直接定义他的对应表的字段:国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值。
                看书去字段的数量比第一个数据库多了很多!但是他也更很全面!
               但是,这个xls在录入时候发现他还有两个问题:
                   1.国家名字里面包含了单引号和& 和 -- 字样,需要把把单引号和and 替换成空格,对 -- 不解析。(sql里面单引号会直接解释字符串的输入,从而后面乱码,and符号会把后面的字符当作变量,--会直接当作注释,从而后面不执行。另外 \ 会被转义,当然这里面没有这个字符所以没关系。)
                  2.最后一列并不是年份,他是 Estimates Start After ,这一列由于和别的列不一样,我们其实可以不要这个字段。

            数据库3,worldbankdatabase
                数据3里面只要看 WDIEXCEL.xlsx 文件即可,我们先瞄一眼,看看他的数据结构是怎么样的。
数据库3的数据结构.png

                数据库3的数据也是十分整齐的结构,所以不需要像前面一样自己去转换数据。我们直接定义他的对应表的字段:国家、国家简称、年份、变量名、变量简称、数据的值。
                数据库3的数据量难道比数据库2的少很多?为什么很多信息比如 单位 都没有?其实他的信息只是保存在了另外的表格里面。仔细看左下角还有好几个sheet表,里面包含了很多附加信息,但是我们没这么精力或者说没必要全部用到,到时用到再回来查也是没问题的!所以暂时就只导入Data这个sheet表。这个xls在录入时候的问题参照前面的方法。

            由前面三个数据库的字段进行总结,我们取所有字段的交集,得到最终我们要的数据库的大表!
                数据库的大表包含的字段为:国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。(注意之所以要加最后一个字段是为了方便区分数据来源。)


创建数据表供vbs导入

    新建一个数据库
        新建一个数据库,专门用于存放这张表,名字为WorldEconometicsDatabase
新建数据库.png
新建数据库.png (29.98 KiB) 查看 843 次
新建数据库.png
新建数据库.png (29.98 KiB) 查看 843 次
新建世界经济数据库.png

        这里建议把数据库的日志改为简单,因为完整的日志模式可能你没玩一会儿,日志就上百 GB 了。因为你的所有删除修改操作都会被记录下来,日志是用来做数据库的恢复的。点击数据库名字右键属性设置:
点击数据库名字右键属性设置日志为简单.png

    新建一个登录名
        新建一个登录名,因为外部程序一般只能用户密码登录数据库,而你平时操作数据经常直接用windows本身的账户登录。
新建登录名.png
新建登录名.png (31.71 KiB) 查看 842 次
新建登录名.png
新建登录名.png (31.71 KiB) 查看 842 次
新建登录名设置.png

    赋数据库的访问权限予我们的登录名
        将之前创建的数据库WorldEconometicsDatabase的访问权限给我们的登录名。(因为这个数据库是我们在windows账户本身的时候创建的,所属是windows这个账户,如果你一开始先创建登录名,登录了以后再创建,就不需要赋权限这一步。)
赋予权限.png
赋予权限.png (35.39 KiB) 查看 842 次
赋予权限.png
赋予权限.png (35.39 KiB) 查看 842 次
赋予权限设置.png

    创建数据库对应的数据表
        数据表包含所有字段:国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。
新建查询.png
新建查询.png (38.9 KiB) 查看 842 次
新建查询.png
新建查询.png (38.9 KiB) 查看 842 次

        输入下面的代码,以创建数据表,其中customer_id是整个表的唯一id,随着数据的录入递增;
        国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。分别为:country countryid countrycode whichyear variablename variablelabel variablecode units scale datafrom independentvariable whichdatabase:

Code: 全选

 USE WorldEconomicsDatabase;
--国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。
--country countryid countrycode whichyear variablename variablelabel variablecode units scale datafrom independentvariable whichdatabase
CREATE TABLE WORLDECONOMICSTABLE(
    country VARCHAR(400),
    countryid INT,
    countrycode VARCHAR(50),
    whichyear INT,
    variablename VARCHAR(400),
    variablelabel VARCHAR(4000),
    variablecode VARCHAR (100),
    units VARCHAR (400),
    scale VARCHAR (400),
    datafrom VARCHAR (4000),
    independentvariable VARCHAR (400),
    whichdatabase VARCHAR (50),
    customer_id  int primary key identity(1,1)) 
        之所以一开始不对数据库的关键字做 not null ,是防止vbs导入数据时候,sql server直接拒绝vbs的输入,同时还不给出任何反馈,导致你不知道为什么数据缺失了的情况发生。如果允许所有字段都可能是空,这样等数据录进去你再去检查为什么哪个不应该为空的字段为空了,就方便得多!
        从而我们就新建了一张大表,用于存放最后整合成功的数据。
        另外,我们还要新建3张小表,用于存放各个数据库的数据。我们给他取名为WORLDECONOMICSTABLE1、WORLDECONOMICSTABLE2、WORLDECONOMICSTABLE3。字段和设置与大表完全一致,所以只要改下上面程序的WORLDECONOMICSTABLE就可以了。
        比如,新建数据库1的表为:

Code: 全选

 CREATE TABLE WORLDECONOMICSTABLE1(
    country VARCHAR(400),
    countryid INT,
    countrycode VARCHAR(50),
    whichyear INT,
    variablename VARCHAR(400),
    variablelabel VARCHAR(4000),
    variablecode VARCHAR (100),
    units VARCHAR (400),
    scale VARCHAR (400),
    datafrom VARCHAR (4000),
    independentvariable VARCHAR (400),
    whichdatabase VARCHAR (50),
    customer_id  int primary key identity(1,1)) 

Link:
Hide post links
Show post links

头像
hellohappy
网站管理员
网站管理员
帖子: 330
注册时间: 2018年11月18日, 14:27
Has thanked: 1 time
Been thanked: 8 time

#2 编写vbs程序,将Excel导入sql server数据库

未读文章 hellohappy » 2019年1月01日, 13:45



第二贴:
编写vbs程序,将Excel导入数据库

    一个关于拖动鼠标就可以执行的vbs模版程序
    用vbs连接sql server 数据库的方法
    vbs链接数据库以后执行sql语句模版
    vbs定义Excle对象、工作薄对象、工作表对象模版
    读取数据库1的Excel内容,导入数据库
    数据库1的全部代码
    读取数据库2的Excel内容,导入数据库
    数据库2的全部代码
    读取数据库3的Excel内容,导入数据库
    数据库3的全部代码
合并三张数据表
    根据三张数据表生成一张包含所有唯一国家和唯一时间的数据表
        首先我们看一下导入数据库以后的数据长什么样
        为了安全起见,当然是建议你操作之前备份一下这3张表
        一些操作sql的tips:
        确定合成的表的样子:
        总结一下前面为了合并三张数据表,而生成唯一id表的所有代码:
    各个数据表生成自己的视图
        数据库1生成自己的视图:
 



编写vbs程序,将Excel导入sql server数据库
    导入第一个数据库的方法详细讲,后面第二第三个数据库参照前面的就可以了

    一个关于拖动鼠标就可以执行的vbs模版程序
        有时候,我们写vbs程序并不希望直接把要操作的对象的路径和名字写死,因为写死了,程序的通用性就会很低,换一个对象就要改一下参数。所以,你可以参考这个小模版,他可以获取被拖进来的文件的路径和文件名(要获取多个路径和文件名,或者双击直接获取文件路径和名字都是可以的,我会开一个主题介绍这个模版程序)。下面代码里面,只要你以vbs为文件名后缀保存,然后拖动别的文件到这个vbs文件图标上面,vbs程序就会自动读取到所有拖动过来的文件的路径和文件名,保存在 strPath 。

Code: 全选

'******************************************
'拖拽文件,获取文件路径
'******************************************
If WScript.Arguments.Count = 0 Then
    MsgBox "拖拽文件到本图标", 0, "提示"
End If

For a = 0 To WScript.Arguments.Count - 1
    strPath = WScript.Arguments(a)
Next

    用vbs连接sql server 数据库的方法
        vbs链接sql server数据库可以直接用下面的程序模版。

Code: 全选

Dim oCon,ExecuteState,strsql
'sql server服务器名称 MS-20170103XXOM
set oCon = createObject("adodb.connection") 
'创建connection对象
oCon.connectionString = "Provider=SQLOLEDB.1;dirver={sql server}; server=MS-20170103XXOM;uid=WorldEconomics;pwd=123456;dataBase=WorldEconomicsDatabase1"
'利用connection对象的connectionString属性 来定义  连接数据库的参数
'参数 dirver 固定为 {sql server}; server是服务器名称; uid、pwd、dataBase 为登录名、登录密码、数据库名
oCon.open  '连接数据库
if oCon.state = 0 then
    msgbox ("SQL Server connection failed")
end if

    vbs链接数据库以后执行sql语句模版
        vbs执行sql语句模版:

Code: 全选

Dim strsql
'strsql变量记录sql的语句
strsql = "INSERT INTO WORLDECONOMICSTABLE1(countryid,country) values("& countryid &",'"& country &"')"
ExecuteState = executesql(oCon,strsql)
If Not IsObject(ExecuteState) then
    msgbox("录入失败")
end if

'*****************************************************************************
'执行sql语句
'*****************************************************************************
Function executesql(oCon,strsql)
    Dim rs
    Set rs = oCon.execute(strsql)
    Set executesql = rs
End Function

    vbs定义Excle对象、工作薄对象、工作表对象模版
        读取Excel里面的工作表模版:

Code: 全选

'******************************************
'定义Excle对象、工作薄对象、工作表对象
'******************************************
dim ws,oExcel,oWb,oSheet,ColumnsCount,RowsCount
Set ws = WScript.CreateObject("wscript.shell")
Set oExcel = CreateObject("Excel.Application")
'打开指定的工作簿
Set oWb = oExcel.Workbooks.Open(strPath)
'显示打开的Excel工作簿
oExcel.Visible = True

'读第一个sheet表
Set oSheet = oWb.Sheets(1)
'显示活动的表
oSheet.activate
'读取有多少行多少列
ColumnsCount=oSheet.UsedRange.Columns.Count
RowsCount=oSheet.UsedRange.Rows.Count

'最后记得关掉整个工作簿对象
oWb.Close()

    读取数据库1的Excel内容,导入数据库
        剩下要讲的就是如何读取数据库1的Excel表的内容了!这个部分已经不难了,只需要用两个循环,一个大循环负责遍历列,一个内循环负责遍历行,这样一张表就遍历完毕了!
        观察Excelname的表格,第一个要读取的单元格为"D3",数值填在independentvariable上;而一个单元格要读取的变量有,前三列的:countryid、country、whichyear,前两行的:variablename、variablelabel;其他的变量全部为空,也就是NA或NULL,注意他不是"";为什么要让列的遍历放在大循环呢?因为列的变量名是一直不变的!放在大循环可以让他一个大循环读取一次变量名,而不是读取每一个数据都去读一遍变量名。节省程序运行时间。

Code: 全选

[size=100]dim country,countryid,countrycode,whichyear,variablename,variablelabel,variablecode,units,scale,datafrom,independentvariable,whichdatabase[/size]
whichdatabase = "IMFfiscalruledatabase"
for j = 4 to ColumnsCount
    variablename = oSheet.cells(1,j).value
    variablelabel = oSheet.cells(2,j).value
    for i = 3 to RowsCount
        tmpvar1 = oSheet.cells(i,j).value
        if tmpvar1<>""then
            independentvariable = tmpvar1
            country = oSheet.cells(i,2).value
            countryid = oSheet.cells(i,1).value
            whichyear = oSheet.cells(i,3).value
            '这里录入数据库
        end if
    next
next

    数据库1的全部代码
        数据库1的全部代码(包含前面sql建表,已经注释),下面程序复制下来直接保存成vbs结尾的后缀格式以后,可以直接拖拽数据库1的Excel文件到该vbs程序,运行。(不要拖拽多个Excel,不然他可能报错,因为下面程序读取文件名字和路径时候直接用这个命令:Set oWb = oExcel.Workbooks.Open(strPath)   ;这个命令就是默认str只有一个记录的,当然你看懂了可以自己随便改。)

Code: 全选

'USE WorldEconomicsDatabase;
'--国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。
'--country countryid countrycode whichyear variablename variablelabel variablecode units scale datafrom independentvariable whichdatabase
'CREATE TABLE WORLDECONOMICSTABLE1(
'    country VARCHAR(400),
'    countryid INT,
'    countrycode VARCHAR(50),
'    whichyear INT,
'    variablename VARCHAR(400),
'    variablelabel VARCHAR(4000),
'    variablecode VARCHAR (100),
'    units VARCHAR (400),
'    scale VARCHAR (400),
'    datafrom VARCHAR (4000),
'    independentvariable VARCHAR (400),
'    whichdatabase VARCHAR (50),
'    customer_id  int primary key identity(1,1)) 


'******************************************
'拖拽文件,获取文件路径
'******************************************
If WScript.Arguments.Count = 0 Then
        MsgBox "拖拽文件到本图标", 0, "提示"
End If
 
  
For a = 0 To WScript.Arguments.Count - 1
 
    strPath = WScript.Arguments(a)
    
Next

Dim oCon,ExecuteState,strsql
'sql server服务器名称 MS-20170103XXOM
set oCon = createObject("adodb.connection") 
'创建connection对象
oCon.connectionString = "Provider=SQLOLEDB.1;dirver={sql server}; server=MS-20170103XXOM;uid=WorldEconomics;pwd=123456;dataBase=WorldEconomicsDatabase"
'利用connection对象的connectionString属性 来定义  连接数据库的参数
'参数  dirver固定为 {sql server}; server是服务器名称  uid、pwd、dataBase、就可以了
'也可以 定义一个  以key:value; 组成的 连接参数字符串str  再通过 oCon.open str  来连接
oCon.open  '连接数据库
if oCon.state = 0 then
    msgbox ("SQL Server connection failed")
end if

'******************************************
'定义Excle对象、工作薄对象、工作表对象
'******************************************
dim ws,oExcel,oWb,oSheet,ColumnsCount,RowsCount,i,j,tmpvar1
Set ws = WScript.CreateObject("wscript.shell")
Set oExcel = CreateObject("Excel.Application")
'打开指定的工作簿
Set oWb = oExcel.Workbooks.Open(strPath)
'显示打开的Excel工作簿
oExcel.Visible = True

Set oSheet = oWb.Sheets(1)
oSheet.activate
ColumnsCount=oSheet.UsedRange.Columns.Count
RowsCount=oSheet.UsedRange.Rows.Count

'观察Excelname的表格
'第一个要读取的单元格为"D3",数值填在independentvariable上
'而一个单元格要读取的变量有,前三列的:countryid、country、whichyear
'前三行的variablename、variablelabel;其他的变量全部为空,也就是NA或NULL,注意他不是"";
dim country,countryid,countrycode,whichyear,variablename,variablelabel,variablecode,units,scale,datafrom,independentvariable,whichdatabase
'i为行指针,j为列指针,为什么要改成先for列呢,因为变量名是一直不变的!不要每次都去读一遍变量名。

whichdatabase = "IMFfiscalruledatabase"
for j = 4 to ColumnsCount
    variablename = oSheet.cells(1,j).value
    variablelabel = oSheet.cells(2,j).value
    for i = 3 to RowsCount
        tmpvar1 = oSheet.cells(i,j).value
        if tmpvar1<>""then
            independentvariable = tmpvar1
            country = oSheet.cells(i,2).value
            countryid = oSheet.cells(i,1).value
            whichyear = oSheet.cells(i,3).value
            '录入数据库
            strsql = "INSERT INTO WORLDECONOMICSTABLE1(countryid,country,whichyear,variablename,variablelabel,independentvariable,whichdatabase) values("& countryid &",'"& country &"',"& whichyear &",'"& variablename &"','"& variablelabel &"','"& independentvariable &"','"& whichdatabase &"')"
            ExecuteState = executesql(oCon,strsql)
            If Not IsObject(ExecuteState) then
                msgbox("录入失败")
            end if
        end if
    next
next

oWb.Close()

'*****************************************************************************
'执行sql语句
'*****************************************************************************
Function executesql(oCon,strsql)
    Dim rs
    Set rs = oCon.execute(strsql)
    Set executesql = rs
End Function

    读取数据库2的Excel内容,导入数据库
        观察WEOOct2018all的表格,第一个要读取的单元格为"J2",数值填在independentvariable上;而一个单元格要读取的变量有,前九列的:countryid、countrycode、variablecode、country、variablename、variablelabel、units、scale、datafrom;每一个单元格的最上方一行:whichyear;其他的变量全部为空,也就是NA或NULL,注意他不是"";

Code: 全选

whichdatabase = "WEOdatabases"
for j = 10 to ColumnsCount
    whichyear = oSheet.cells(1,j).value
    for i = 2 to RowsCount
        tmpvar1 = oSheet.cells(i,j).value
        if (tmpvar1<>"")and(tmpvar1<>"n/a")and(tmpvar1<>"--")then
            independentvariable = tmpvar1
            countryid = oSheet.cells(i,1).value
            countrycode = oSheet.cells(i,2).value
            variablecode = oSheet.cells(i,3).value
            country = oSheet.cells(i,4).value
            variablename = oSheet.cells(i,5).value
            variablelabel = oSheet.cells(i,6).value
            units = oSheet.cells(i,7).value
            scale = oSheet.cells(i,8).value
            datafrom = oSheet.cells(i,9).value
            '录入数据库
        end if
    next
next

    数据库2的全部代码
        数据库2的全部代码(包含前面sql建表,已经注释),下面程序复制下来直接保存成vbs结尾的后缀格式以后,可以直接拖拽数据库2的Excel文件到该vbs程序,运行。

Code: 全选

'USE WorldEconomicsDatabase;
'--国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。
'--country countryid countrycode whichyear variablename variablelabel variablecode units scale datafrom independentvariable whichdatabase
'CREATE TABLE WORLDECONOMICSTABLE2(
'    country VARCHAR(400),
'    countryid INT,
'    countrycode VARCHAR(50),
'    whichyear INT,
'    variablename VARCHAR(400),
'    variablelabel VARCHAR(4000),
'    variablecode VARCHAR (100),
'    units VARCHAR (400),
'    scale VARCHAR (400),
'    datafrom VARCHAR (4000),
'    independentvariable VARCHAR (400),
'    whichdatabase VARCHAR (50),
'    customer_id  int primary key identity(1,1))


'******************************************
'拖拽文件,获取文件路径
'******************************************
If WScript.Arguments.Count = 0 Then
        MsgBox "拖拽文件到本图标", 0, "提示"
End If
 
  
For a = 0 To WScript.Arguments.Count - 1
 
    strPath = WScript.Arguments(a)
    
Next

Dim oCon,ExecuteState,strsql
'sql server服务器名称 MS-20170103XXOM
set oCon = createObject("adodb.connection") 
'创建connection对象
oCon.connectionString = "Provider=SQLOLEDB.1;dirver={sql server}; server=MS-20170103XXOM;uid=WorldEconomics;pwd=123456;dataBase=WorldEconomicsDatabase"
'利用connection对象的connectionString属性 来定义  连接数据库的参数
'参数  dirver固定为 {sql server}; server是服务器名称  uid、pwd、dataBase、就可以了
'也可以 定义一个  以key:value; 组成的 连接参数字符串str  再通过 oCon.open str  来连接
oCon.open  '连接数据库
if oCon.state = 0 then
    msgbox ("SQL Server connection failed")
end if

'******************************************
'定义Excle对象、工作薄对象、工作表对象
'******************************************
dim ws,oExcel,oWb,oSheet,ColumnsCount,RowsCount,i,j,tmpvar1
Set ws = WScript.CreateObject("wscript.shell")
Set oExcel = CreateObject("Excel.Application")
'打开指定的工作簿
Set oWb = oExcel.Workbooks.Open(strPath)
'显示打开的Excel工作簿
oExcel.Visible = True

Set oSheet = oWb.Sheets(1)
oSheet.activate
ColumnsCount=oSheet.UsedRange.Columns.Count
RowsCount=oSheet.UsedRange.Rows.Count



'观察WEOOct2018all的表格
'第一个要读取的单元格为"J2",数值填在independentvariable上
'而一个单元格要读取的变量有,前九列的:countryid、countrycode、variablecode、country、variablename、variablelabel、units、scale、datafrom
'每一个单元格的最上方一行:whichyear
'其他的变量全部为空,也就是NA或NULL,注意他不是"";
dim country,countryid,countrycode,whichyear,variablename,variablelabel,variablecode,units,scale,datafrom,independentvariable,whichdatabase
'i为行指针,j为列指针,为什么要改成先for列呢,因为年份是一直不变的!不要每次都去读一遍年份。

whichdatabase = "WEOdatabases"
for j = 10 to ColumnsCount
    whichyear = oSheet.cells(1,j).value
    for i = 2 to RowsCount
        tmpvar1 = oSheet.cells(i,j).value
        if (tmpvar1<>"")and(tmpvar1<>"n/a")and(tmpvar1<>"--")then
            independentvariable = tmpvar1
            countryid = oSheet.cells(i,1).value
            countrycode = oSheet.cells(i,2).value
            variablecode = oSheet.cells(i,3).value
            country = oSheet.cells(i,4).value
            variablename = oSheet.cells(i,5).value
            variablelabel = oSheet.cells(i,6).value
            units = oSheet.cells(i,7).value
            scale = oSheet.cells(i,8).value
            datafrom = oSheet.cells(i,9).value
            '录入数据库
            strsql = "INSERT INTO WORLDECONOMICSTABLE2(countryid,countrycode,variablecode,country,variablename,variablelabel,units,scale,datafrom,whichyear,independentvariable,whichdatabase) values("& countryid &",'"& countrycode &"','"& variablecode &"','"& country &"','"& variablename &"','"& variablelabel &"','"& units &"','"& scale &"','"& datafrom &"',"& whichyear &",'"& independentvariable &"','"& whichdatabase &"')"
            ExecuteState = executesql(oCon,strsql)
            If Not IsObject(ExecuteState) then
                msgbox("录入失败")
            end if
        end if
    next
next

oWb.Close()

'*****************************************************************************
'执行sql语句
'*****************************************************************************
Function executesql(oCon,strsql)
    Dim rs
    Set rs = oCon.execute(strsql)
    Set executesql = rs
End Function

    读取数据库3的Excel内容,导入数据库
        观察WDIEXCEL的表格,第一个要读取的单元格为"E2",数值填在independentvariable上;而一个单元格要读取的变量有,前四列的:country、countrycode、variablename、variablecode;第一行:whichyear;其他的变量全部为空,也就是NA或NULL,注意他不是"";

Code: 全选

whichdatabase = "worldbankdatabase"
for j = 5 to ColumnsCount
    whichyear = oSheet.cells(1,j).value
    for i = 2 to RowsCount
        tmpvar1 = oSheet.cells(i,j).value
        if (tmpvar1<>"")and(tmpvar1<>"n/a")and(tmpvar1<>"--")then
            independentvariable = tmpvar1
            country = oSheet.cells(i,1).value
            countrycode = oSheet.cells(i,2).value
            variablename = oSheet.cells(i,3).value
            variablecode = oSheet.cells(i,4).value
            '录入数据库
        end if
    next
next

    数据库3的全部代码
        数据库3的全部代码(包含前面sql建表,已经注释),下面程序复制下来直接保存成vbs结尾的后缀格式以后,可以直接拖拽数据库3的Excel文件到该vbs程序,运行。

Code: 全选

'USE WorldEconomicsDatabase;
'--国家、国家编号、国家简称、年份、变量名、变量别名、变量简称、单位、单位的规模、数据来源、数据的值、数据属于哪个数据库。
'--country countryid countrycode whichyear variablename variablelabel variablecode units scale datafrom independentvariable whichdatabase
'CREATE TABLE WORLDECONOMICSTABLE3(
'    country VARCHAR(400),
'    countryid INT,
'    countrycode VARCHAR(50),
'    whichyear INT,
'    variablename VARCHAR(400),
'    variablelabel VARCHAR(4000),
'    variablecode VARCHAR (100),
'    units VARCHAR (400),
'    scale VARCHAR (400),
'    datafrom VARCHAR (4000),
'    independentvariable VARCHAR (400),
'    whichdatabase VARCHAR (50),
'    customer_id  int primary key identity(1,1))


'******************************************
'拖拽文件,获取文件路径
'******************************************
If WScript.Arguments.Count = 0 Then
        MsgBox "拖拽文件到本图标", 0, "提示"
End If
 
  
For a = 0 To WScript.Arguments.Count - 1
 
    strPath = WScript.Arguments(a)
    
Next

Dim oCon,ExecuteState,strsql
'sql server服务器名称 MS-20170103XXOM
set oCon = createObject("adodb.connection") 
'创建connection对象
oCon.connectionString = "Provider=SQLOLEDB.1;dirver={sql server}; server=MS-20170103XXOM;uid=WorldEconomics;pwd=123456;dataBase=WorldEconomicsDatabase"
'利用connection对象的connectionString属性 来定义  连接数据库的参数
'参数  dirver固定为 {sql server}; server是服务器名称  uid、pwd、dataBase、就可以了
'也可以 定义一个  以key:value; 组成的 连接参数字符串str  再通过 oCon.open str  来连接
oCon.open  '连接数据库
if oCon.state = 0 then
    msgbox ("SQL Server connection failed")
end if

'******************************************
'定义Excle对象、工作薄对象、工作表对象
'******************************************
dim ws,oExcel,oWb,oSheet,ColumnsCount,RowsCount,i,j,tmpvar1
Set ws = WScript.CreateObject("wscript.shell")
Set oExcel = CreateObject("Excel.Application")
'打开指定的工作簿
Set oWb = oExcel.Workbooks.Open(strPath)
'显示打开的Excel工作簿
oExcel.Visible = True

Set oSheet = oWb.Sheets(1) '刚好Data就是第一张sheet表
oSheet.activate
ColumnsCount=oSheet.UsedRange.Columns.Count
RowsCount=oSheet.UsedRange.Rows.Count



'观察WDIEXCEL的表格
'第一个要读取的单元格为"E2",数值填在independentvariable上
'而一个单元格要读取的变量有,前四列的:country、countrycode、variablename、variablecode;一行whichyear
'其他的变量全部为空,也就是NA或NULL,注意他不是"";
dim country,countryid,countrycode,whichyear,variablename,variablelabel,variablecode,units,scale,datafrom,independentvariable,whichdatabase
'i为行指针,j为列指针,为什么要改成先for列呢,因为年份是一直不变的!不要每次都去读一遍年份。

whichdatabase = "worldbankdatabase"
for j = 5 to ColumnsCount
    whichyear = oSheet.cells(1,j).value
    for i = 2 to RowsCount
        tmpvar1 = oSheet.cells(i,j).value
        if (tmpvar1<>"")and(tmpvar1<>"n/a")and(tmpvar1<>"--")then
            independentvariable = tmpvar1
            country = oSheet.cells(i,1).value
            countrycode = oSheet.cells(i,2).value
            variablename = oSheet.cells(i,3).value
            variablecode = oSheet.cells(i,4).value
            '录入数据库
            strsql = "INSERT INTO WORLDECONOMICSTABLE3(country,countrycode,variablename,variablecode,whichyear,independentvariable,whichdatabase) values('"& country &"','"& countrycode &"','"& variablename &"','"& variablecode &"',"& whichyear &",'"& independentvariable &"','"& whichdatabase &"')"
            ExecuteState = executesql(oCon,strsql)
            If Not IsObject(ExecuteState) then
                msgbox("录入失败")
            end if
        end if
    next
next

oWb.Close()

'*****************************************************************************
'执行sql语句
'*****************************************************************************
Function executesql(oCon,strsql)
    Dim rs
    Set rs = oCon.execute(strsql)
    Set executesql = rs
End Function

合并三张数据表
    根据三张数据表生成一张包含所有唯一国家和唯一时间的数据表
        首先我们看一下导入数据库以后的数据长什么样,下面分别是数据库1,2,3:
导入数据库的数据1.png
导入数据库的数据2.png
导入数据库的数据3.png

        三张数据表里面,whichyear字段是可以完全匹配的,因为所有的数据表这个字段都是数字,而且表达同一个信息,但是要让数据作为面板数据的形式进行分析,就必须要对个体(截面),也就是国家进行匹配。这里所有表都有的字段叫 country ,也就是国家名。所以,我们可以通过匹配 whichyear 和 country 来连接三张表,但是由于每一张表里面,国家名字可能不太相同,部分国家可能还需要我们人工拼接。

        为了安全起见,当然是建议你操作之前备份一下这3张表,语句如下:比如备份数据库1的表:select * into WORLDECONOMICSTABLE1_backup from WORLDECONOMICSTABLE1 ;他就相当于帮你复制了一张一样的表,毕竟这几张表导入数据库花了差不多一天的时间!(当然,不排除是我程序写的烂才导入这么久)

        一些操作sql的tips:
            1.然后不管是生成表还是修改字段,想要在sql server的资源管理器上面看到效果,都要记得先刷新一下。
            2.如果你习惯在sql server的客户端查询页面打代码执行sql程序,记得给这类似的这种有破坏性的语句前加注释,drop update insert等等。否则一不小心点了F5执行,但是又没有选中任何语句,会导致全部语句一起被执行了,然后很可能就凉凉。
            3.好的习惯是,每句sql语句后面都加分号;英文的分号。
            4.sql server里面加注释的快捷键是 ctrl+k,ctrl+c;去掉注释快捷键:ctrl+k,ctrl+u;(我是这么记的: 由于我手写注释写了n久才发现原来有快捷键!气愤的说了一句:“靠!再见!”;再见就是see you啦,所以就是 k c u)

        确定合成的表的样子:
            我要研究的东西,最重要的是数据库1,也就是 IMFfiscalruledatabase ,因为里面包含了各种财政政策(我的核心解释变量)。剩下的两个数据库,都是为了与该数据库进行匹配,然后再做其他的研究。所以我有三个计划的数据库样式:
                1.数据库1与数据库2的交集(指country),时间直接取并集即可,也就是1980-2023
                2.数据库1与数据库3的交集(指country),时间直接取并集即可,也就是1960-2017
                3.数据库1与数据库2、数据库3的交集(指country),时间直接取并集即可,也就是1960-2023
            所以,先写了下面的代码查看交集到底有多少:

Code: 全选

--各个表有多少个国家名字插入临时表
select distinct country into #tmp from WORLDECONOMICSTABLE1 ;--96个国家
select distinct country into #tmp2 from WORLDECONOMICSTABLE2;--194个国家
select distinct country into #tmp3 from WORLDECONOMICSTABLE3;--263个国家
--依次查看交集
select a.country from #tmp as a, #tmp2 as b where a.country = b.country;--91个国家
select a.country from #tmp as a, #tmp3 as b where a.country = b.country;--89个国家
select a.country from #tmp as a, #tmp2 as b, #tmp3 as c where a.country = b.country and a.country = c.country;--89个国家
            既然三个的交集有89个,没比两个少太多,干脆直接用三个的交集好了。生成临时表 #tmp4,用于记下国家的交集,#tmp5,用于记录时间的并集,以方便后面操作:

Code: 全选

--生成临时表4
create table #tmp4(
    country VARCHAR(400),
    customer_id  int primary key identity(1,1));
insert  into #tmp4(country) select a.country from #tmp as a, #tmp2 as b, #tmp3 as c where a.country = b.country and a.country = c.country order by a.country;

--生成临时表5
drop table #tmp,#tmp2,#tmp3;
select distinct whichyear into #tmp from WORLDECONOMICSTABLE1 order by whichyear;
select distinct whichyear into #tmp2 from WORLDECONOMICSTABLE2 order by whichyear;
select distinct whichyear into #tmp3 from WORLDECONOMICSTABLE3 order by whichyear;
select whichyear into #tmp5 from #tmp
    union
        select whichyear from #tmp2
    union
        select whichyear from #tmp3
    order by whichyear

--删掉没用的临时表
drop table #tmp,#tmp2,#tmp3;
            所以,国家包括89个,时间为1960年到2023年。

        生成一张表 ThreeDatabasesUnion ,包含国家id,全表唯一id
            生成一张表,包含国家,时间,国家id和全表唯一id(也可以加入其他信息,但是最简就是只有id和相应字段。):

Code: 全选

use THEECONOMICSDatabase;
create table ThreeDatabasesUnion(
    country VARCHAR(400),
    country_id int,
    whichyear int,
    customer_id  int primary key identity(1,1));
insert into ThreeDatabasesUnion(country,country_id,whichyear) select distinct a.country,a.customer_id,b.whichyear from #tmp4 as a, #tmp5 as b order by a.customer_id,b.whichyear;
        总结一下前面为了合并三张数据表,而生成唯一id表的所有代码:

Code: 全选

--select distinct country into #tmp from WORLDECONOMICSTABLE1 ;--96个国家
--select distinct country into #tmp2 from WORLDECONOMICSTABLE2;--194个国家
--select distinct country into #tmp3 from WORLDECONOMICSTABLE3;--263个国家
--select a.country from #tmp as a, #tmp2 as b where a.country = b.country;--91个国家
--select a.country from #tmp as a, #tmp3 as b where a.country = b.country;--89个国家
--select a.country from #tmp as a, #tmp2 as b, #tmp3 as c where a.country = b.country and a.country = c.country;--89个国家

--create table #tmp4(
--    country VARCHAR(400),
--    customer_id  int primary key identity(1,1));
--insert  into #tmp4(country) select a.country from #tmp as a, #tmp2 as b, #tmp3 as c where a.country = b.country and a.country = c.country order by a.country;
--drop table #tmp,#tmp2,#tmp3;
--select distinct whichyear into #tmp from WORLDECONOMICSTABLE1 order by whichyear;
--select distinct whichyear into #tmp2 from WORLDECONOMICSTABLE2 order by whichyear;
--select distinct whichyear into #tmp3 from WORLDECONOMICSTABLE3 order by whichyear;
--select whichyear into #tmp5 from #tmp
--    union
--        select whichyear from #tmp2
--    union
--        select whichyear from #tmp3
--    order by whichyear
--drop table #tmp,#tmp2,#tmp3;

--use WorldEconomicsDatabase;
--create table ThreeDatabasesUnion(
--    country VARCHAR(400),
--    country_id int,
--    whichyear int,
--    customer_id  int primary key identity(1,1));
--insert into ThreeDatabasesUnion(country,country_id,whichyear) select distinct a.country,a.customer_id,b.whichyear from #tmp4 as a, #tmp5 as b order by a.customer_id,b.whichyear;

    各个数据表生成自己的视图
        数据库1生成自己的视图:
            由于我写的是通用的模版程序,所以可能有些地方比较长,这个程序可以用于剩下的两个数据表的视图生成,其中--线分割的部分需要单独运行,一次运行一块。各个块的功能在注释里面写的很清楚了。下面涉及到了索引视图,这主要是因为,视图过多,如果不建立索引,查询速度慢的不行! 

Code: 全选

--用sql语句提取数据,并排列整齐
    use WorldEconomicsDatabase

-------------------------------------------------------------------------
--用-----线分割的部分请单独运行
-------------------------------------------------------------------------
--生成73个索引视图用于合并
    --设置sql server,使得其可以建立索引视图,以下只需执行一次
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        SET CONCAT_NULL_YIELDS_NULL ON
        SET QUOTED_IDENTIFIER ON
        SET NUMERIC_ROUNDABORT OFF
    --找到所有的变量名、声明变量
        select distinct variablename into #TempTableVarName from WORLDECONOMICSTABLE1 order by variablename
        --select * from #TempTableVarName
        DECLARE @VarName VARCHAR(400);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
    --建立游标遍历变量名字用以生成73张视图用于合并
        DECLARE CursorVarName CURSOR FOR
            SELECT variablename FROM #TempTableVarName
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database1VarNameView' + cast(@Num as varchar);
            --EXEC('drop view ' + @ViewName
            --    );
            EXEC(
                'CREATE VIEW '+ @ViewName +' WITH SCHEMABINDING AS '
                +'select a.country,b.country_id,a.countrycode,a.whichyear,a.independentvariable as ''' + @VarName
                + ''' from dbo.WORLDECONOMICSTABLE1 as a,dbo.ThreeDatabasesUnion as b
                where a.country = b.country and a.whichyear = b.whichyear and a.variablename = ''' + @VarName + ''';'
                );
            FETCH NEXT FROM CursorVarName INTO @VarName;
        END
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
        drop table #TempTableVarName
-------------------------------------------------------------------------
--生成一张表,包含完整的时间和地区id
    select * into YEARANDREGIONID from ThreeDatabasesUnion
--再根据表YEARANDREGIONID、原来的表生成一张包含完整信息的表,除了没有变量信息
--(country_id来自于三个表联合,而countryid来自于数据库本身)
    CREATE TABLE InformationOtherThanVariableNames(
        country VARCHAR(400),
        countryid INT,
        country_id INT,
        countrycode VARCHAR(50),
        whichyear INT,
        whichdatabase VARCHAR (50),
        customer_id  int primary key identity(1,1)) 
    --给InformationOtherThanVariableNames插入数据
        insert into InformationOtherThanVariableNames
            select distinct a.country,b.countryid,a.country_id,b.countrycode,
            a.whichyear,b.whichdatabase
            from YEARANDREGIONID as a,WORLDECONOMICSTABLE1 as b
            where a.country = b.country and a.whichyear = b.whichyear order by a.country_id,a.whichyear
--删掉临时表
    drop table YEARANDREGIONID
-------------------------------------------------------------------------
--为了加快数据库的视图生成,需要对数据添加索引
    --添加InformationOtherThanVariableNames的两个索引
        CREATE unique nonclustered INDEX AccelerateIndexForInformationOtherThanVariableNames1
            ON InformationOtherThanVariableNames(country_id,whichyear);
    --找到所有的变量名、声明变量
        select distinct variablename into #TempTableVarName from WORLDECONOMICSTABLE1 order by variablename
        --select * from #TempTableVarName
        DECLARE @VarName NVARCHAR(300);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
    --建立游标遍历变量名字用以生成73张视图的唯一聚集索引
        DECLARE CursorVarName CURSOR FOR
            SELECT variablename FROM #TempTableVarName
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database1VarNameView' + cast(@Num as varchar);
            EXEC(
                'CREATE unique clustered INDEX AccelerateIndexFor' + @ViewName
                + ' ON ' + @ViewName + '(country_id,whichyear);'
                );
            FETCH NEXT FROM CursorVarName INTO @VarName;
        END
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
        drop table #TempTableVarName
-------------------------------------------------------------------------
--根据表InformationOtherThanVariableNames 和 前面生成的73个视图,进行合并,形成真正的输出表
    --找到所有的变量名、声明变量
        select distinct variablename into #TempTableVarName from WORLDECONOMICSTABLE1 order by variablename
        --select * from #TempTableVarName
        DECLARE @VarName NVARCHAR(300);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
        DECLARE @tempstr NVARCHAR(4000) = '';
        DECLARE @tempstr2 NVARCHAR(4000) = '';
        DECLARE @tempstr3 NVARCHAR(4000) = '';
        DECLARE @tempstr4 NVARCHAR(4000) = '';
        DECLARE @tempstr5 NVARCHAR(4000) = '';
        DECLARE @tempstr6 NVARCHAR(4000) = '';
    --建立游标遍历变量名字用以生成73张视图用于合并
        DECLARE CursorVarName CURSOR FOR
            SELECT variablename FROM #TempTableVarName
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database1VarNameView' + cast(@Num as varchar);
            SELECT @tempstr = @tempstr + ',' + @ViewName + '.[' + @VarName + ']'
            IF (@Num < 25)
            BEGIN
                SELECT @tempstr2 = @tempstr2 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 45)
            BEGIN
                SELECT @tempstr3 = @tempstr3 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 65)
            BEGIN
                SELECT @tempstr4 = @tempstr4 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 85)
            BEGIN
                SELECT @tempstr5 = @tempstr5 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 105)
            BEGIN
                SELECT @tempstr6 = @tempstr6 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
        END
    --组合字符串形成命令并执行,生成最终的视图
        print(@tempstr2)
        print(@tempstr3)
        print(@tempstr4)
        print(@tempstr5)
        print(@tempstr6)
        print(
            'select a.country,a.countryid,a.country_id,a.countrycode,a.whichyear,a.whichdatabase,a.customer_id'
            + @tempstr + ' into Database1RESULTVIEW'
            + ' from InformationOtherThanVariableNames as a' + @tempstr2 + @tempstr3 + @tempstr4 + @tempstr5 + @tempstr6 + ' order by a.customer_id'
            );
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
    drop table #TempTableVarName

-------------------------------------------------------------------------
--由于直接执行效率很低(直接执行的方法是把print改成EXEC,但是他最多只能运行4000个字符,也就是已经超长了)
--,我试着分段运行,依次注释掉其中一部分
-------------------------------------------------------------------------
select a.country,a.countryid,a.country_id,a.countrycode,a.whichyear,a.whichdatabase,a.customer_id
--,Database1VarNameView1.[enforce_s_ER],Database1VarNameView2.[enforce_n_DR],Database1VarNameView3.[monitor_s_BBR],Database1VarNameView4.[enforce_s_DR],Database1VarNameView5.[legal_s_DR],Database1VarNameView6.[monitor_n_BBR],Database1VarNameView7.[legal_n_ER],Database1VarNameView8.[eu],Database1VarNameView9.[DR_year],Database1VarNameView10.[cover_s_BBR],Database1VarNameView11.[suport_ceil_n_i],Database1VarNameView12.[region],Database1VarNameView13.[escap_s_DR],Database1VarNameView14.[emerg],Database1VarNameView15.[resour],Database1VarNameView16.[suport_ceil_n_a],Database1VarNameView17.[monitor_s_ER],Database1VarNameView18.[escap_n_ER],Database1VarNameView19.[stab_n],Database1VarNameView20.[legal_s_BBR],Database1VarNameView21.[monitor_s_RR],Database1VarNameView22.[enforce_n_ER],Database1VarNameView23.[BBR_year],Database1VarNameView24.[legal_n_RR]
--,Database1VarNameView25.[monitor_n_ER],Database1VarNameView26.[monitor_s_DR],Database1VarNameView27.[lic],Database1VarNameView28.[RR_year],Database1VarNameView29.[DR_supra],Database1VarNameView30.[rule],Database1VarNameView31.[monitor_n_DR],Database1VarNameView32.[cur],Database1VarNameView33.[escap_n_BBR],Database1VarNameView34.[stab_s],Database1VarNameView35.[escap_n_RR],Database1VarNameView36.[legal_n_BBR],Database1VarNameView37.[ER_year],Database1VarNameView38.[enforce_s_BBR],Database1VarNameView39.[escap_s_ER],Database1VarNameView40.[adv],Database1VarNameView41.[escap_s_RR],Database1VarNameView42.[cover_s_ER],Database1VarNameView43.[no_invest_n],Database1VarNameView44.[ER]
,Database1VarNameView45.[legal_s_ER],Database1VarNameView46.[ER_supra],Database1VarNameView47.[cover_n_DR],Database1VarNameView48.[legal_n_DR],Database1VarNameView49.[DR],Database1VarNameView50.[cover_s_DR],Database1VarNameView51.[escap_n_DR],Database1VarNameView52.[BBR_supra],Database1VarNameView53.[RR_supra],Database1VarNameView54.[fed],Database1VarNameView55.[cover_n_BBR],Database1VarNameView56.[cover_n_RR],Database1VarNameView57.[escap_s_BBR],Database1VarNameView58.[RR],Database1VarNameView59.[nrule],Database1VarNameView60.[legal_s_RR],Database1VarNameView61.[cover_n_ER],Database1VarNameView62.[nrule_nat],Database1VarNameView63.[frl],Database1VarNameView64.[suport_ceil_n_m]
,Database1VarNameView65.[monitor_n_RR],Database1VarNameView66.[enforce_n_BBR],Database1VarNameView67.[no_invest_s],Database1VarNameView68.[cover_s_RR],Database1VarNameView69.[enforce_n_RR],Database1VarNameView70.[suport_impl_n],Database1VarNameView71.[suport_budg_n],Database1VarNameView72.[BBR],Database1VarNameView73.[enforce_s_RR] 
into Database1RESULTVIEW3 from InformationOtherThanVariableNames as a

 --FULL OUTER JOIN Database1VarNameView1 WITH (NOEXPAND) on a.country_id=Database1VarNameView1.country_id and a.whichyear=Database1VarNameView1.whichyear
 --FULL OUTER JOIN Database1VarNameView2 WITH (NOEXPAND) on a.country_id=Database1VarNameView2.country_id and a.whichyear=Database1VarNameView2.whichyear
 --FULL OUTER JOIN Database1VarNameView3 WITH (NOEXPAND) on a.country_id=Database1VarNameView3.country_id and a.whichyear=Database1VarNameView3.whichyear
 --FULL OUTER JOIN Database1VarNameView4 WITH (NOEXPAND) on a.country_id=Database1VarNameView4.country_id and a.whichyear=Database1VarNameView4.whichyear
 --FULL OUTER JOIN Database1VarNameView5 WITH (NOEXPAND) on a.country_id=Database1VarNameView5.country_id and a.whichyear=Database1VarNameView5.whichyear
 --FULL OUTER JOIN Database1VarNameView6 WITH (NOEXPAND) on a.country_id=Database1VarNameView6.country_id and a.whichyear=Database1VarNameView6.whichyear
 --FULL OUTER JOIN Database1VarNameView7 WITH (NOEXPAND) on a.country_id=Database1VarNameView7.country_id and a.whichyear=Database1VarNameView7.whichyear
 --FULL OUTER JOIN Database1VarNameView8 WITH (NOEXPAND) on a.country_id=Database1VarNameView8.country_id and a.whichyear=Database1VarNameView8.whichyear
 --FULL OUTER JOIN Database1VarNameView9 WITH (NOEXPAND) on a.country_id=Database1VarNameView9.country_id and a.whichyear=Database1VarNameView9.whichyear
 --FULL OUTER JOIN Database1VarNameView10 WITH (NOEXPAND) on a.country_id=Database1VarNameView10.country_id and a.whichyear=Database1VarNameView10.whichyear
 --FULL OUTER JOIN Database1VarNameView11 WITH (NOEXPAND) on a.country_id=Database1VarNameView11.country_id and a.whichyear=Database1VarNameView11.whichyear
 --FULL OUTER JOIN Database1VarNameView12 WITH (NOEXPAND) on a.country_id=Database1VarNameView12.country_id and a.whichyear=Database1VarNameView12.whichyear
 --FULL OUTER JOIN Database1VarNameView13 WITH (NOEXPAND) on a.country_id=Database1VarNameView13.country_id and a.whichyear=Database1VarNameView13.whichyear
 --FULL OUTER JOIN Database1VarNameView14 WITH (NOEXPAND) on a.country_id=Database1VarNameView14.country_id and a.whichyear=Database1VarNameView14.whichyear
 --FULL OUTER JOIN Database1VarNameView15 WITH (NOEXPAND) on a.country_id=Database1VarNameView15.country_id and a.whichyear=Database1VarNameView15.whichyear
 --FULL OUTER JOIN Database1VarNameView16 WITH (NOEXPAND) on a.country_id=Database1VarNameView16.country_id and a.whichyear=Database1VarNameView16.whichyear
 --FULL OUTER JOIN Database1VarNameView17 WITH (NOEXPAND) on a.country_id=Database1VarNameView17.country_id and a.whichyear=Database1VarNameView17.whichyear
 --FULL OUTER JOIN Database1VarNameView18 WITH (NOEXPAND) on a.country_id=Database1VarNameView18.country_id and a.whichyear=Database1VarNameView18.whichyear
 --FULL OUTER JOIN Database1VarNameView19 WITH (NOEXPAND) on a.country_id=Database1VarNameView19.country_id and a.whichyear=Database1VarNameView19.whichyear
 --FULL OUTER JOIN Database1VarNameView20 WITH (NOEXPAND) on a.country_id=Database1VarNameView20.country_id and a.whichyear=Database1VarNameView20.whichyear
 --FULL OUTER JOIN Database1VarNameView21 WITH (NOEXPAND) on a.country_id=Database1VarNameView21.country_id and a.whichyear=Database1VarNameView21.whichyear
 --FULL OUTER JOIN Database1VarNameView22 WITH (NOEXPAND) on a.country_id=Database1VarNameView22.country_id and a.whichyear=Database1VarNameView22.whichyear
 --FULL OUTER JOIN Database1VarNameView23 WITH (NOEXPAND) on a.country_id=Database1VarNameView23.country_id and a.whichyear=Database1VarNameView23.whichyear
 --FULL OUTER JOIN Database1VarNameView24 WITH (NOEXPAND) on a.country_id=Database1VarNameView24.country_id and a.whichyear=Database1VarNameView24.whichyear

 --FULL OUTER JOIN Database1VarNameView25 WITH (NOEXPAND) on a.country_id=Database1VarNameView25.country_id and a.whichyear=Database1VarNameView25.whichyear
 --FULL OUTER JOIN Database1VarNameView26 WITH (NOEXPAND) on a.country_id=Database1VarNameView26.country_id and a.whichyear=Database1VarNameView26.whichyear
 --FULL OUTER JOIN Database1VarNameView27 WITH (NOEXPAND) on a.country_id=Database1VarNameView27.country_id and a.whichyear=Database1VarNameView27.whichyear
 --FULL OUTER JOIN Database1VarNameView28 WITH (NOEXPAND) on a.country_id=Database1VarNameView28.country_id and a.whichyear=Database1VarNameView28.whichyear
 --FULL OUTER JOIN Database1VarNameView29 WITH (NOEXPAND) on a.country_id=Database1VarNameView29.country_id and a.whichyear=Database1VarNameView29.whichyear
 --FULL OUTER JOIN Database1VarNameView30 WITH (NOEXPAND) on a.country_id=Database1VarNameView30.country_id and a.whichyear=Database1VarNameView30.whichyear
 --FULL OUTER JOIN Database1VarNameView31 WITH (NOEXPAND) on a.country_id=Database1VarNameView31.country_id and a.whichyear=Database1VarNameView31.whichyear
 --FULL OUTER JOIN Database1VarNameView32 WITH (NOEXPAND) on a.country_id=Database1VarNameView32.country_id and a.whichyear=Database1VarNameView32.whichyear
 --FULL OUTER JOIN Database1VarNameView33 WITH (NOEXPAND) on a.country_id=Database1VarNameView33.country_id and a.whichyear=Database1VarNameView33.whichyear
 --FULL OUTER JOIN Database1VarNameView34 WITH (NOEXPAND) on a.country_id=Database1VarNameView34.country_id and a.whichyear=Database1VarNameView34.whichyear
 --FULL OUTER JOIN Database1VarNameView35 WITH (NOEXPAND) on a.country_id=Database1VarNameView35.country_id and a.whichyear=Database1VarNameView35.whichyear
 --FULL OUTER JOIN Database1VarNameView36 WITH (NOEXPAND) on a.country_id=Database1VarNameView36.country_id and a.whichyear=Database1VarNameView36.whichyear
 --FULL OUTER JOIN Database1VarNameView37 WITH (NOEXPAND) on a.country_id=Database1VarNameView37.country_id and a.whichyear=Database1VarNameView37.whichyear
 --FULL OUTER JOIN Database1VarNameView38 WITH (NOEXPAND) on a.country_id=Database1VarNameView38.country_id and a.whichyear=Database1VarNameView38.whichyear
 --FULL OUTER JOIN Database1VarNameView39 WITH (NOEXPAND) on a.country_id=Database1VarNameView39.country_id and a.whichyear=Database1VarNameView39.whichyear
 --FULL OUTER JOIN Database1VarNameView40 WITH (NOEXPAND) on a.country_id=Database1VarNameView40.country_id and a.whichyear=Database1VarNameView40.whichyear
 --FULL OUTER JOIN Database1VarNameView41 WITH (NOEXPAND) on a.country_id=Database1VarNameView41.country_id and a.whichyear=Database1VarNameView41.whichyear
 --FULL OUTER JOIN Database1VarNameView42 WITH (NOEXPAND) on a.country_id=Database1VarNameView42.country_id and a.whichyear=Database1VarNameView42.whichyear
 --FULL OUTER JOIN Database1VarNameView43 WITH (NOEXPAND) on a.country_id=Database1VarNameView43.country_id and a.whichyear=Database1VarNameView43.whichyear
 --FULL OUTER JOIN Database1VarNameView44 WITH (NOEXPAND) on a.country_id=Database1VarNameView44.country_id and a.whichyear=Database1VarNameView44.whichyear

 FULL OUTER JOIN Database1VarNameView45 WITH (NOEXPAND) on a.country_id=Database1VarNameView45.country_id and a.whichyear=Database1VarNameView45.whichyear
 FULL OUTER JOIN Database1VarNameView46 WITH (NOEXPAND) on a.country_id=Database1VarNameView46.country_id and a.whichyear=Database1VarNameView46.whichyear
 FULL OUTER JOIN Database1VarNameView47 WITH (NOEXPAND) on a.country_id=Database1VarNameView47.country_id and a.whichyear=Database1VarNameView47.whichyear
 FULL OUTER JOIN Database1VarNameView48 WITH (NOEXPAND) on a.country_id=Database1VarNameView48.country_id and a.whichyear=Database1VarNameView48.whichyear
 FULL OUTER JOIN Database1VarNameView49 WITH (NOEXPAND) on a.country_id=Database1VarNameView49.country_id and a.whichyear=Database1VarNameView49.whichyear
 FULL OUTER JOIN Database1VarNameView50 WITH (NOEXPAND) on a.country_id=Database1VarNameView50.country_id and a.whichyear=Database1VarNameView50.whichyear
 FULL OUTER JOIN Database1VarNameView51 WITH (NOEXPAND) on a.country_id=Database1VarNameView51.country_id and a.whichyear=Database1VarNameView51.whichyear
 FULL OUTER JOIN Database1VarNameView52 WITH (NOEXPAND) on a.country_id=Database1VarNameView52.country_id and a.whichyear=Database1VarNameView52.whichyear
 FULL OUTER JOIN Database1VarNameView53 WITH (NOEXPAND) on a.country_id=Database1VarNameView53.country_id and a.whichyear=Database1VarNameView53.whichyear
 FULL OUTER JOIN Database1VarNameView54 WITH (NOEXPAND) on a.country_id=Database1VarNameView54.country_id and a.whichyear=Database1VarNameView54.whichyear
 FULL OUTER JOIN Database1VarNameView55 WITH (NOEXPAND) on a.country_id=Database1VarNameView55.country_id and a.whichyear=Database1VarNameView55.whichyear
 FULL OUTER JOIN Database1VarNameView56 WITH (NOEXPAND) on a.country_id=Database1VarNameView56.country_id and a.whichyear=Database1VarNameView56.whichyear
 FULL OUTER JOIN Database1VarNameView57 WITH (NOEXPAND) on a.country_id=Database1VarNameView57.country_id and a.whichyear=Database1VarNameView57.whichyear
 FULL OUTER JOIN Database1VarNameView58 WITH (NOEXPAND) on a.country_id=Database1VarNameView58.country_id and a.whichyear=Database1VarNameView58.whichyear
 FULL OUTER JOIN Database1VarNameView59 WITH (NOEXPAND) on a.country_id=Database1VarNameView59.country_id and a.whichyear=Database1VarNameView59.whichyear
 FULL OUTER JOIN Database1VarNameView60 WITH (NOEXPAND) on a.country_id=Database1VarNameView60.country_id and a.whichyear=Database1VarNameView60.whichyear
 FULL OUTER JOIN Database1VarNameView61 WITH (NOEXPAND) on a.country_id=Database1VarNameView61.country_id and a.whichyear=Database1VarNameView61.whichyear
 FULL OUTER JOIN Database1VarNameView62 WITH (NOEXPAND) on a.country_id=Database1VarNameView62.country_id and a.whichyear=Database1VarNameView62.whichyear
 FULL OUTER JOIN Database1VarNameView63 WITH (NOEXPAND) on a.country_id=Database1VarNameView63.country_id and a.whichyear=Database1VarNameView63.whichyear
 FULL OUTER JOIN Database1VarNameView64 WITH (NOEXPAND) on a.country_id=Database1VarNameView64.country_id and a.whichyear=Database1VarNameView64.whichyear

 FULL OUTER JOIN Database1VarNameView65 WITH (NOEXPAND) on a.country_id=Database1VarNameView65.country_id and a.whichyear=Database1VarNameView65.whichyear
 FULL OUTER JOIN Database1VarNameView66 WITH (NOEXPAND) on a.country_id=Database1VarNameView66.country_id and a.whichyear=Database1VarNameView66.whichyear
 FULL OUTER JOIN Database1VarNameView67 WITH (NOEXPAND) on a.country_id=Database1VarNameView67.country_id and a.whichyear=Database1VarNameView67.whichyear
 FULL OUTER JOIN Database1VarNameView68 WITH (NOEXPAND) on a.country_id=Database1VarNameView68.country_id and a.whichyear=Database1VarNameView68.whichyear
 FULL OUTER JOIN Database1VarNameView69 WITH (NOEXPAND) on a.country_id=Database1VarNameView69.country_id and a.whichyear=Database1VarNameView69.whichyear
 FULL OUTER JOIN Database1VarNameView70 WITH (NOEXPAND) on a.country_id=Database1VarNameView70.country_id and a.whichyear=Database1VarNameView70.whichyear
 FULL OUTER JOIN Database1VarNameView71 WITH (NOEXPAND) on a.country_id=Database1VarNameView71.country_id and a.whichyear=Database1VarNameView71.whichyear
 FULL OUTER JOIN Database1VarNameView72 WITH (NOEXPAND) on a.country_id=Database1VarNameView72.country_id and a.whichyear=Database1VarNameView72.whichyear
 FULL OUTER JOIN Database1VarNameView73 WITH (NOEXPAND) on a.country_id=Database1VarNameView73.country_id and a.whichyear=Database1VarNameView73.whichyear

 order by a.customer_id
 -----------------------------------------------------------------------------------
 select * from Database1RESULTVIEW as a ,Database1RESULTVIEW2 as b ,Database1RESULTVIEW3 as c 
    where a.country_id = b.country_id and a.whichyear = b.whichyear and a.country_id = c.country_id and a.whichyear = c.whichyear
    order by a.customer_id
--复制出来即可

--后记:
------------------------------------------------------------------------------------
--最后经过测试发现一次性运行耗时过长,分多次运行效率比较高,也就是约等于每22个变量放一起
-------------------------------------------------------------------------


 

Link:
Hide post links
Show post links

头像
hellohappy
网站管理员
网站管理员
帖子: 330
注册时间: 2018年11月18日, 14:27
Has thanked: 1 time
Been thanked: 8 time

#3 续上一贴

未读文章 hellohappy » 2019年1月02日, 20:16



目录:
    数据库1的视图生成
    数据库2的视图生成代码
    数据库3的视图生成代码
        完成上面这么多步骤,你应该有以下这么多数据表:

    合并三个数据表的视图
        合并第三个数据库成5个视图:
        合并三个数据库成1个视图:

    输出数据表到Excel
    对excel表略加整理
        本帖和上一贴相关附件



    由于上一贴的代码太长,后面编辑不太方便,再开一贴继续上一贴的内容

    数据库1的视图生成

       不妨来欣赏一下,视图转换时候有多耗电脑(电脑4核八线程的i7 4710mq ,16GB 内存):
数据库的查询转换有多消耗电脑.png

    数据库2的视图生成代码
       数据库2和数据库1代码几乎一样,只是变量的唯一表示不是变量名字,而是变量的code。code又短又不重复!
       代码如下(由于生成的字符串很长,我觉得那一部分没必要贴出来就不贴了,最后还有全部代码集合可以下载):

Code: 全选

--数据库2要若以变量名和单位一起唯一识别变量,会导致程序发生较大改变
--,但是单纯以变量名又不能唯一标识变量,所以使用变量code是最好的!

--用sql语句提取数据,并排列整齐
    use WorldEconomicsDatabase
--看看有整体上有哪些数据

-------------------------------------------------------------------------
--用-----线分割的部分请单独运行
-------------------------------------------------------------------------
--生成73个索引视图用于合并
    --设置sql server,使得其可以建立索引视图,以下只需执行一次
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        SET CONCAT_NULL_YIELDS_NULL ON
        SET QUOTED_IDENTIFIER ON
        SET NUMERIC_ROUNDABORT OFF
    --找到所有的变量名、声明变量
        select distinct variablecode into #TempTableVarName from WORLDECONOMICSTABLE2 order by variablecode
        --select * from #TempTableVarName
        DECLARE @VarName VARCHAR(400);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
    --建立游标遍历变量名字用以生成73张视图用于合并
        DECLARE CursorVarName CURSOR FOR
            SELECT variablecode FROM #TempTableVarName order by variablecode
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database2VarNameView' + cast(@Num as varchar);
            --EXEC('drop view ' + @ViewName
            --    );
            EXEC(
                'CREATE VIEW '+ @ViewName +' WITH SCHEMABINDING AS '
                +'select a.country,b.country_id,a.countrycode,a.whichyear,a.independentvariable as ''' + @VarName
                + ''' from dbo.WORLDECONOMICSTABLE2 as a,dbo.ThreeDatabasesUnion as b
                where a.country = b.country and a.whichyear = b.whichyear and a.variablecode = ''' + @VarName + ''';'
                );
            FETCH NEXT FROM CursorVarName INTO @VarName;
        END
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
        drop table #TempTableVarName
-------------------------------------------------------------------------
--生成一张表,包含完整的时间和地区id
    select * into YEARANDREGIONID from ThreeDatabasesUnion
--再根据表YEARANDREGIONID、原来的表生成一张包含完整信息的表,除了没有变量信息
--(country_id来自于三个表联合,而countryid来自于数据库本身)
    CREATE TABLE InformationOtherThanVariableNames2(
        country VARCHAR(400),
        countryid INT,
        country_id INT,
        countrycode VARCHAR(50),
        whichyear INT,
        whichdatabase VARCHAR (50),
        customer_id  int primary key identity(1,1)) 
    --给InformationOtherThanVariableNames插入数据
        insert into InformationOtherThanVariableNames2
            select distinct a.country,b.countryid,a.country_id,b.countrycode,
            a.whichyear,b.whichdatabase
            from YEARANDREGIONID as a,WORLDECONOMICSTABLE2 as b
            where a.country = b.country and a.whichyear = b.whichyear order by a.country_id,a.whichyear
--删掉临时表
    drop table YEARANDREGIONID
-------------------------------------------------------------------------
--为了加快数据库的视图生成,需要对数据添加索引
    --添加InformationOtherThanVariableNames的两个索引
        CREATE unique nonclustered INDEX AccelerateIndexForInformationOtherThanVariableNames2
            ON InformationOtherThanVariableNames2(country_id,whichyear);
    --找到所有的变量名、声明变量
        select distinct variablecode into #TempTableVarName from WORLDECONOMICSTABLE2 order by variablecode
        --select * from #TempTableVarName
        DECLARE @VarName NVARCHAR(300);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
    --建立游标遍历变量名字用以生成73张视图的唯一聚集索引
        DECLARE CursorVarName CURSOR FOR
            SELECT variablecode FROM #TempTableVarName
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database2VarNameView' + cast(@Num as varchar);
            EXEC(
                'CREATE unique clustered INDEX AccelerateIndexFor' + @ViewName
                + ' ON ' + @ViewName + '(country_id,whichyear);'
                );
            FETCH NEXT FROM CursorVarName INTO @VarName;
        END
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
        drop table #TempTableVarName
-------------------------------------------------------------------------
--根据表InformationOtherThanVariableNames 和 前面生成的73个视图,进行合并,形成真正的输出表
    --找到所有的变量名、声明变量
        select distinct variablecode into #TempTableVarName from WORLDECONOMICSTABLE2 order by variablecode
        --select * from #TempTableVarName
        DECLARE @VarName NVARCHAR(300);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
        DECLARE @tempstr NVARCHAR(4000) = '';
        DECLARE @tempstr2 NVARCHAR(4000) = '';
        DECLARE @tempstr3 NVARCHAR(4000) = '';
        DECLARE @tempstr4 NVARCHAR(4000) = '';
        DECLARE @tempstr5 NVARCHAR(4000) = '';
        DECLARE @tempstr6 NVARCHAR(4000) = '';
    --建立游标遍历变量名字用以生成73张视图用于合并
        DECLARE CursorVarName CURSOR FOR
            SELECT variablecode FROM #TempTableVarName order by variablecode
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database2VarNameView' + cast(@Num as varchar);
            SELECT @tempstr = @tempstr + ',' + @ViewName + '.[' + @VarName + ']'
            IF (@Num < 25)
            BEGIN
                SELECT @tempstr2 = @tempstr2 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 45)
            BEGIN
                SELECT @tempstr3 = @tempstr3 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 65)
            BEGIN
                SELECT @tempstr4 = @tempstr4 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 85)
            BEGIN
                SELECT @tempstr5 = @tempstr5 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 105)
            BEGIN
                SELECT @tempstr6 = @tempstr6 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
        END
    --组合字符串形成命令并执行,生成最终的视图
        print(@tempstr2)
        print(@tempstr3)
        print(@tempstr4)
        print(@tempstr5)
        print(@tempstr6)
        print(
            'select a.country,a.countryid,a.country_id,a.countrycode,a.whichyear,a.whichdatabase,a.customer_id'
            + @tempstr + ' into Database2RESULTVIEW'
            + ' from InformationOtherThanVariableNames2 as a' + @tempstr2 + @tempstr3 + @tempstr4 + @tempstr5 + @tempstr6 + ' order by a.customer_id'
            );
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
    drop table #TempTableVarName

-------------------------------------------------------------------------
--由于直接执行效率很低,我试着直接运行
-------------------------------------------------------------------------
--略
 -----------------------------------------------------------------------------------
 select * from Database2RESULTVIEW as a ,Database2RESULTVIEW2 as b,Database2RESULTVIEW3 as c
    where a.country_id = b.country_id and a.whichyear = b.whichyear and a.country_id = c.country_id and a.whichyear = c.whichyear
    order by a.customer_id
--复制出来即可

    数据库3的视图生成代码
       数据库3和数据库1代码几乎一样,只是变量的唯一表示不是变量名字,而是变量的code。code又短又不重复!
       代码如下(由于生成的字符串很长,我觉得那一部分没必要贴出来就不贴了,最后还有全部代码集合可以下载):

Code: 全选

--数据库3要若以变量名唯一识别变量,会导致变量名太长,
--sql server最多支持128长度所以也使用变量code

--用sql语句提取数据,并排列整齐
    use WorldEconomicsDatabase
--看看有整体上有哪些数据

-------------------------------------------------------------------------
--用-----线分割的部分请单独运行
-------------------------------------------------------------------------
--生成73个索引视图用于合并
    --设置sql server,使得其可以建立索引视图,以下只需执行一次
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
        SET CONCAT_NULL_YIELDS_NULL ON
        SET QUOTED_IDENTIFIER ON
        SET NUMERIC_ROUNDABORT OFF
    --找到所有的变量名、声明变量
        select distinct variablecode into #TempTableVarName from WORLDECONOMICSTABLE3 order by variablecode
        --select * from #TempTableVarName
        DECLARE @VarName VARCHAR(400);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
    --建立游标遍历变量名字用以生成73张视图用于合并
        DECLARE CursorVarName CURSOR FOR
            SELECT variablecode FROM #TempTableVarName order by variablecode
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database3VarNameView' + cast(@Num as varchar);
            --EXEC('drop view ' + @ViewName
            --    );
            EXEC(
                'CREATE VIEW '+ @ViewName +' WITH SCHEMABINDING AS '
                +'select a.country,b.country_id,a.countrycode,a.whichyear,a.independentvariable as ''' + @VarName
                + ''' from dbo.WORLDECONOMICSTABLE3 as a,dbo.ThreeDatabasesUnion as b
                where a.country = b.country and a.whichyear = b.whichyear and a.variablecode = ''' + @VarName + ''';'
                );
            FETCH NEXT FROM CursorVarName INTO @VarName;
        END
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
        drop table #TempTableVarName
-------------------------------------------------------------------------
--生成一张表,包含完整的时间和地区id
    select * into YEARANDREGIONID from ThreeDatabasesUnion
--再根据表YEARANDREGIONID、原来的表生成一张包含完整信息的表,除了没有变量信息
--(country_id来自于三个表联合,而countryid来自于数据库本身)
    CREATE TABLE InformationOtherThanVariableNames3(
        country VARCHAR(400),
        countryid INT,
        country_id INT,
        countrycode VARCHAR(50),
        whichyear INT,
        whichdatabase VARCHAR (50),
        customer_id  int primary key identity(1,1)) 
    --给InformationOtherThanVariableNames插入数据
        insert into InformationOtherThanVariableNames3
            select distinct a.country,b.countryid,a.country_id,b.countrycode,
            a.whichyear,b.whichdatabase
            from YEARANDREGIONID as a,WORLDECONOMICSTABLE3 as b
            where a.country = b.country and a.whichyear = b.whichyear order by a.country_id,a.whichyear
--删掉临时表
    drop table YEARANDREGIONID
-------------------------------------------------------------------------
--为了加快数据库的视图生成,需要对数据添加索引
    --添加InformationOtherThanVariableNames的两个索引
        CREATE unique nonclustered INDEX AccelerateIndexForInformationOtherThanVariableNames3
            ON InformationOtherThanVariableNames3(country_id,whichyear);
    --找到所有的变量名、声明变量
        select distinct variablecode into #TempTableVarName from WORLDECONOMICSTABLE3 order by variablecode
        --select * from #TempTableVarName
        DECLARE @VarName NVARCHAR(300);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
    --建立游标遍历变量名字用以生成73张视图的唯一聚集索引
        DECLARE CursorVarName CURSOR FOR
            SELECT variablecode FROM #TempTableVarName
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database3VarNameView' + cast(@Num as varchar);
            EXEC(
                'CREATE unique clustered INDEX AccelerateIndexFor' + @ViewName
                + ' ON ' + @ViewName + '(country_id,whichyear);'
                );
            FETCH NEXT FROM CursorVarName INTO @VarName;
        END
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
        drop table #TempTableVarName
-------------------------------------------------------------------------
--根据表InformationOtherThanVariableNames 和 前面生成的73个视图,进行合并,形成真正的输出表
    --找到所有的变量名、声明变量
        select distinct variablecode into #TempTableVarName from WORLDECONOMICSTABLE3 order by variablecode
        --select * from #TempTableVarName
        DECLARE @VarName NVARCHAR(300);
        DECLARE @Num INT = 0;
        DECLARE @ViewName NVARCHAR(50);
        DECLARE @tempstr NVARCHAR(4000) = '';
        DECLARE @tempstr2 NVARCHAR(4000) = '';
        DECLARE @tempstr3 NVARCHAR(4000) = '';
        DECLARE @tempstr4 NVARCHAR(4000) = '';
        DECLARE @tempstr5 NVARCHAR(4000) = '';
        DECLARE @tempstr6 NVARCHAR(4000) = '';
    --建立游标遍历变量名字用以生成73张视图用于合并
        DECLARE CursorVarName CURSOR FOR
            SELECT variablecode FROM #TempTableVarName order by variablecode
        OPEN CursorVarName;
        FETCH NEXT FROM CursorVarName INTO @VarName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @Num = @Num + 1;
            SELECT @ViewName = 'Database3VarNameView' + cast(@Num as varchar);
            SELECT @tempstr = @tempstr + ',' + @ViewName + '.[' + @VarName + ']'
            IF (@Num < 25)
            BEGIN
                SELECT @tempstr2 = @tempstr2 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 45)
            BEGIN
                SELECT @tempstr3 = @tempstr3 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 65)
            BEGIN
                SELECT @tempstr4 = @tempstr4 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 85)
            BEGIN
                SELECT @tempstr5 = @tempstr5 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
            ELSE IF (@Num < 105)
            BEGIN
                SELECT @tempstr6 = @tempstr6 + char(13)+char(10) + ' FULL OUTER JOIN ' + @ViewName + ' WITH (NOEXPAND) on a.country_id=' 
                    + @ViewName + '.country_id and a.whichyear='+ @ViewName + '.whichyear'
                FETCH NEXT FROM CursorVarName INTO @VarName;
            END
        END
    --组合字符串形成命令并执行,生成最终的视图
        print(@tempstr2)
        print(@tempstr3)
        print(@tempstr4)
        print(@tempstr5)
        print(@tempstr6)
        print(
            'select a.country,a.countryid,a.country_id,a.countrycode,a.whichyear,a.whichdatabase,a.customer_id'
            + @tempstr + ' into Database3RESULTVIEW'
            + ' from InformationOtherThanVariableNames3 as a' + @tempstr2 + @tempstr3 + @tempstr4 + @tempstr5 + @tempstr6 + ' order by a.customer_id'
            );
    --删除遍历变量名的游标
        CLOSE CursorVarName;
        DEALLOCATE CursorVarName;
    --删除临时表
    drop table #TempTableVarName

-------------------------------------------------------------------------
--由于直接执行效率很低,我试着直接运行
-------------------------------------------------------------------------
--略


        完成上面这么多步骤,你应该有以下这么多数据表:
此时你应该有哪些数据表1.png
此时你应该有哪些数据表1.png (17.28 KiB) 查看 778 次
此时你应该有哪些数据表1.png
此时你应该有哪些数据表1.png (17.28 KiB) 查看 778 次
此时你应该有哪些数据表2.png
此时你应该有哪些数据表2.png (19.36 KiB) 查看 778 次
此时你应该有哪些数据表2.png
此时你应该有哪些数据表2.png (19.36 KiB) 查看 778 次
此时你应该有哪些数据表3.png
此时你应该有哪些数据表3.png (19.42 KiB) 查看 778 次
此时你应该有哪些数据表3.png
此时你应该有哪些数据表3.png (19.42 KiB) 查看 778 次
此时你应该有哪些数据表4.png
此时你应该有哪些数据表4.png (19.46 KiB) 查看 778 次
此时你应该有哪些数据表4.png
此时你应该有哪些数据表4.png (19.46 KiB) 查看 778 次
此时你应该有哪些数据表5.png
此时你应该有哪些数据表5.png (31.27 KiB) 查看 778 次
此时你应该有哪些数据表5.png
此时你应该有哪些数据表5.png (31.27 KiB) 查看 778 次

        另外你应该还有接近两千张视图,接近两千个视图索引。不过这个不重要,最重要的是你的数据表,已经基本成型了!

    合并三个数据表的视图
       
理论上这里开始我们已经可以直接合并之前生成的视图了,但是为了加快效率,特别是第3个数据库,被分成了80个视图表,直接合并可能有困难。于是,我们可以先把第三个数据库的80个视图合并成5个视图,再全部一起合并。
        合并第三个数据库成5个视图:

Code: 全选

--本sql主要是用于合并数据库3过多的视图表(80张),合并成5张中表

--select * from InformationOtherThanVariableNames3 as a, Database3RESULTVIEW1 as a1 ,Database3RESULTVIEW2 as a2,Database3RESULTVIEW3 as a3
    --where a.country_id = a1.country_id and a.whichyear = a1.whichyear 
    --    and a.country_id = a2.country_id and a.whichyear = a2.whichyear 
    --    and a.country_id = a3.country_id and a.whichyear = a3.whichyear
    --order by a.customer_id
--模仿上面的语句
--构造如下语句 select @s @ss into Database3RESULTVIEW1to16 from @s2 where @s3 order by a.customer_id
use WorldEconomicsDatabase;
declare @s varchar(8000);
declare @ss varchar(8000)='';
declare @s2 varchar(8000);
declare @s3 varchar(8000);
DECLARE @ViewName NVARCHAR(50);
declare @i int =1;
set @s='select a.country,a.countryid,a.country_id,a.countrycode,a.whichyear,a.whichdatabase,a.customer_id';
set @s2=' InformationOtherThanVariableNames3 as a';
set @s3='';
--1到16号合并表起名为 Database3RESULTVIEW1to16;同理可得:
--Database3RESULTVIEW17to32,Database3RESULTVIEW33to48,
--Database3RESULTVIEW49to64,Database3RESULTVIEW65to80
--每一张表,都只需要修改 
--1.set @i=1; 2.while @i<=16 3.if(@i<=8) 4.exec语句里面的表名
-- 即可!
set @i=65;
while @i<=80
begin
    SELECT @ViewName = 'Database3RESULTVIEW' + cast(@i as varchar);
    if(@i<=72)
    begin
        select @s=isnull(@s+',','')+'a'+ cast(@i as varchar) + '.'+quotename(Name) from syscolumns 
            where ID=object_id(@ViewName) and 
                Name not in('country','countryid','country_id','countrycode','whichyear','whichdatabase','customer_id') 
                    order by colid ;
    end
    else
    begin
        select @ss=isnull(@ss+',','')+'a'+ cast(@i as varchar) + '.'+quotename(Name) from syscolumns 
            where ID=object_id(@ViewName) and 
                Name not in('country','countryid','country_id','countrycode','whichyear','whichdatabase','customer_id') 
                    order by colid ;
    end
    select @s2=@s2+ ',' + @ViewName + ' as a' + cast(@i as varchar);
    select @s3=@s3+ 'a.country_id=a' + cast(@i as varchar) + '.country_id and a.whichyear=a' + cast(@i as varchar) + '.whichyear and ';
    set @i=@i+1
end
select @s3=left(@s3,len(@s3)-4);
exec(
    @s + @ss + ' into Database3RESULTVIEW65to80 from' + @s2 + ' where ' + @s3 + ' order by a.customer_id'
)
--检查5张表的列数是不是正常
--合并表起名为 Database3RESULTVIEW1to16,
--Database3RESULTVIEW17to32,Database3RESULTVIEW33to48,
--Database3RESULTVIEW49to64,Database3RESULTVIEW65to80
--select count(*) from syscolumns s  where s.id = object_id('Database3RESULTVIEW65to80');
        合并三个数据库成1个视图:

Code: 全选

use WorldEconomicsDatabase;
select * from 
    ThreeDatabasesUnion as p

    --LEFT JOIN Database1RESULTVIEW as p1
    --on p.country = p1.country and p.whichyear = p1.whichyear
    --LEFT JOIN Database1RESULTVIEW2 as p2
    --on p.country = p2.country and p.whichyear = p2.whichyear
    --LEFT JOIN Database1RESULTVIEW3 as p3
    --on p.country = p3.country and p.whichyear = p3.whichyear

    --LEFT JOIN Database2RESULTVIEW as p4
    --on p.country = p4.country and p.whichyear = p4.whichyear
    --LEFT JOIN Database2RESULTVIEW2 as p5
    --on p.country = p5.country and p.whichyear = p5.whichyear
    --LEFT JOIN Database2RESULTVIEW3 as p6
    --on p.country = p6.country and p.whichyear = p6.whichyear

    --LEFT JOIN Database3RESULTVIEW1to16 as p7
    --on p.country = p7.country and p.whichyear = p7.whichyear
    --LEFT JOIN Database3RESULTVIEW17to32 as p8
    --on p.country = p8.country and p.whichyear = p8.whichyear
    --LEFT JOIN Database3RESULTVIEW33to48 as p9
    --on p.country = p9.country and p.whichyear = p9.whichyear
    LEFT JOIN Database3RESULTVIEW49to64 as p10
    on p.country = p10.country and p.whichyear = p10.whichyear
    LEFT JOIN Database3RESULTVIEW65to80 as p11
    on p.country = p11.country and p.whichyear = p11.whichyear

    order by p.customer_id

--以上句子是正确的!但是还是报错,因为在SQL SERVER中,记录Record是顺序存放在数据页中的,
--每一个数据页的可存储空间最大为8096字节(8K-页头的96字节,8192-96=8096),
--而SQL Server中行是不能跨数据页的。有两个解决办法哦!
--1.既然是对的,我们把select * 改成我们要的变量,就可以啦,
--这样就可以轻松得到我们自己要的变量,又不会超过最长长度;
--2.分批select嘛!反正是left join,而且已经根据p.customer_id排序了,所有顺序都是一样的!
--分批复制到Excel里面慢慢处理就ok啦!

    输出数据表到Excel
        直接复制粘贴到Excel即可!
复制粘贴数据出来.png

    对excel表略加整理
调一下颜色_方便你自己查看.png
像我这样稍微整理一下按数据库分成三大列.png


本贴和上一贴相关附件:


最终合并后的数据请直接访问该贴--》世界年度经济面板数据

 

Link:
Hide post links
Show post links


回复