从外部EXCEl文件导入sqlserver数据库操作命令
exec sp_configure ‘show advanced options‘,1 reconfigure exec sp_configure ‘Ad Hoc Distributed Queries‘,1 reconfigure go select * into abc1_1 from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0‘ ,‘Excel 5.0;HDR=YES;DATABASE=文件路径‘,SQLResults$)
注意:文件路径到excel下某个固定的sheet,sheet名字不要有空格
数据库合并
insert into [新数据库名(合并后的)] select [字段] FROM [数据库] union select [字段] FROM [数据库] union ...
一般性查询语句
建立视图可以快速查询合并后的数据 select count(1) from [数据库名] --查询个数 select top 50 percent * from 表名 order by 排序字段 -- 只查前50%的数据 select distinct * from dbo.v_AllUser where 服务区域 = ‘杭州供电公司‘ and 联系电话 = ‘15397048235‘ select "联系电话" from dbo.v_AllUser where "业务类型" = ‘投诉‘ and 服务区域 = ‘杭州供电公司‘ group by "联系电话" having count(distinct "工作单编号")=3 select tb.* from ( select "联系电话" from dbo.v_AllUser where "业务类型"=‘投诉‘ and 服务区域 = ‘杭州供电公司‘ group by "联系电话" having count(distinct "工作单编号")=3 ) ta, dbo.v_AllUser tb where ta."联系电话"=tb."联系电话"
修改日期格式语句
select distinct [联系电话],[所属区县],[服务区域], replace(CONVERT(VARCHAR(19),受理时间,120),‘-‘,‘‘) as 受理时间 ,[业务类型],case when 业务子类 =‘‘ then ‘-‘ else 业务子类 end as [业务子类], 0 as "是否按5" from dbo.v_AllUser where 服务区域 = ‘杭州供电公司‘ and 联系电话 <>‘‘ and [所属区县] <>‘‘ and [服务区域] <>‘‘ and [受理时间] <>‘‘ and [业务类型] <>‘‘ order by 受理时间 /*下面这个求得是加入业务子类的非话务表语句, 受理时间[)的作用*/ select [联系电话],[所属区县],[服务区域], replace(CONVERT(VARCHAR(19),受理时间,120),‘-‘,‘‘) as 受理时间,[业务类型], max(case when 业务子类 =‘‘ then ‘-‘ else 业务子类 end )as [业务子类], case when 投诉一级分类 =‘‘ then ‘-‘ else 投诉一级分类 end as [投诉一级分类], 0 as "是否按5" from dbo.v_AllUser where 服务区域 = ‘杭州供电公司‘ and 联系电话 <>‘‘ and [所属区县] <>‘‘ and [服务区域] <>‘‘ and [受理时间] <>‘‘ and [业务类型] <>‘‘ and 受理时间 between ‘20131001‘ and ‘20140101‘ group by 联系电话,[所属区县],[服务区域],[受理时间],[业务类型],[投诉一级分类] order by 受理时间
去除字段文本中特殊的换行字符
update HZ_XX set [受理内容] = replace(replace([受理内容],char(13),‘‘),char(10),‘‘)
或者参考
一定要注意聚合函数的练习,品味下面sql语句的作用
select [联系电话],[所属区县],[服务区域], replace(CONVERT(VARCHAR(19),受理时间,120),‘-‘,‘‘) as 受理时间, 投诉一级分类, [业务类型], max(case when 业务子类 =‘‘ then ‘-‘ else 业务子类 end )as [业务子类], 0 as "是否按5" from dbo.v_AllUser where 服务区域 = ‘杭州供电公司‘ and 联系电话 <>‘‘ and [所属区县] <>‘‘ and [服务区域] <>‘‘ and [受理时间] <>‘‘ and [业务类型] = ‘投诉‘ and 受理时间 between ‘20140101‘ and ‘20140401‘ group by 联系电话,[所属区县],[服务区域],[受理时间],[业务类型],投诉一级分类 having count(distinct‘联系电话‘)=1 order by 联系电话,受理时间