SQL语句学习笔记

浏览数：27 / 时间：2015年06月12日

从外部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 联系电话,受理时间