SQL语句之语法汇总
来源:互联网 发布:美元指数行情软件 编辑:程序博客网 时间:2024/04/29 05:06
前段时间安装了sqlserver及management,编写了一些sql语句,现在对sql中常用的几个语法进行总结、分析与代码实例演示。
汇总一只介绍基本语法,较复杂的排序、分组等操作将在之后的文章中陆续总结!
一.创建表、修改表与删除表
1.1代码
1.创建表:create table Person5(Id int not null,Name nvarchar(50),Age int null)2.修改表:alter table T_Employee add FSubCompany varchar(20); --表中添加分公司alter table T_Employee add FDepartment varchar(20); --表中添加部门3.删除表:drop table person4;
1.2分析
1)create实现在代码中直接生成表,表的字段可以再表名(Person5)后面逐一定义,格式为:字段名 类型 为空与否;
2)alter是在表中添加新的字段,定义其名称和类型;
3)drop将整个表删掉。
------------------------------------------------
博主经营一家发饰淘宝店,都是纯手工制作哦,开业冲钻,只为信誉!需要的亲们可以光顾一下!谢谢大家的支持!
店名:
小鱼尼莫手工饰品店
经营:
发饰、头花、发夹、耳环等(手工制作)
网店:
http://shop117066935.taobao.com/
---------------------------------------------------------------------
继续正题...
二.数据的添加、更新与删除
2.1代码
1.添加数据:--1)一般添加:insert into Person1(Id,Name,Age)values(3,'小王',20);--2)id设置主键后,自动生成,id项不必insert操作:insert into Person3(Name,Age)values('lily',20); --正确insert into Person3 values('john',30); --正确--3)id设置主键后,自动生成,若使用guid类型,需要程序员干预生成:create table Person5(Id uniqueidentifier not null,Name nvarchar(50),Age int null);insert into Person4(Id,Name,Age)values(newid(),'tom',30); --正确insert into Person4(Name,Age)values('tom',30); --错误
2.更新数据--1)无条件更新:update Person1 set Age=20; --更新一个列/字段update Person1 set Age=30,Name='xiaoxiao' --更新多个列/字段 update Person1 set Age=Age+1; --2)条件更新 update Person1 set NickName=N'青年人' where Age>=20; update Person1 set NickName=N'二十岁' where Age=20; --等于 只有一个等号 update Person1 set NickName=N'非二十岁' where Age<>20; --不等于 update Person1 set NickName=N'非二十岁' where (Age>20 and Age<30)or Age=20; --不等于 and or not
3.删除表中某些内容--1)删除表中的全部数据delete from Person1;--2)删除表中符合条件的数据delete from Person1 where Age>=30;
2.2分析
1)name等字段可在属性中设置默认值,当不添加时会使用默认值;
guid做主键,数据插入的顺序与实际排列的顺序不同;Id项需要添加进去后,点击sql执行,才能将guid添加进去!
在数据添加第二种情况下,两种写法都正确!即多项insert时后面可省略字段名称,但不建议如此书写!
有些人喜欢讲into去掉,直接用inset Person(...);也不会报错,但加上into更规范一些!
2)执行update时,如果没有where条件,则将表中所有相应字段的所有值都更新;另外,update可以执行计算语句,如:update Person1 set Age=Age+1;
注意在sql语句中逻辑运算符的使用:等于号为=,不等于号为<>,并且为and,或者为or!
当sql中出现中文时,在中文字符串前加上N,如 NickName=N'青年人'。
3)与前文中drop方法不同,drop直接将表删除;而delete实现对表中满足特定条件(where Age>=30)数据的删除操作。
三.select查询
3.1代码
1.简单selectselect FName from T_Employee; --单字段查询 select FNumber,FName,FAge,FSalary from T_Employee; --多字段查询select * from T_Employee; --所有字段查询2.条件selectselect FName,FSalary from T_Employee where FSalary<5000; --按工资条件查询select * from T_Employee where FSalary<5000;3.可以给列加“别名”,运算操作也可以执行:select FName as 姓名 ,FSalary+10000 as 月薪 from T_Employee where FSalary<5000;4.范围a)多个单值的等价方式select * from T_Employee where FAge=23 or FAge=26 or FAge=28; --or条件选择,等价于下方 select * from T_Employee where FAge in (23,26,28);b)范围值的等价方式select * from T_Employee where FAge>=20 and FAge<=25; --范围,等价于下方select * from T_Employee where FAge between 20 and 25; --包含边界点!,不好用,当条件为FAge>20 and FAge<25时即不可用!
3.2分析:
select就是从某表中,获取到满足条件的结果,涉及到单条件及多条件语句,查询结果显示也可利用别名等进行设定。
四.SQL聚合函数
4.1代码:
select MAX(FSalary) from T_Employee ;select SUM(FSalary) from T_Employee where FAge>25;
4.2分析:
1)SQL聚合函数:MAX(最大值)、MIN(最小值)、AVG(平均值)、SUM(和)、COUNT(数量)。
3)聚合函数不是实际意义上对表的聚合;而是对查询结果的聚合。
2)代码为:查询最高工资、25岁以上工资之和。
继前一篇介绍SQL的简单语法后,本片将主要介绍实际应用中常用的语法,如数据排序order by、通配符Like过滤、空值处理、数据分组group by、限制结果集行数top 5*、子查询、去掉数据重复distinct、联合结果集union/union all、数字函数、字符串函数、日期函数、类型转换函数cast /convert、索引、表连接join等,其中会结合具体代码及例子予以说明。(考虑文章长度,部分内容将放大汇总三)
为方便查看示例select语句,文章最后会附上需要创建的table表:
一.数据分组group by(聚合函数)
1.1代码:
1.group by简单语句--统计每个年龄段的人数1). select FAge,count(*)as 人数 from T_Employee group by FAge; --right2). select FAge,FSalary,count(*) from T_Employee group by FAge; --error--程序报错:选择列表中的列 'T_Employee.FSalary' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。--分析:按照age分组,同一个age可能有好几个工资水平,计算机无法确定显示哪个而报错!3). select FAge,max(FSalary)as 最高工资,count(*) as 人数 from T_Employee group by FAge; --right!--分析:显示出age每组的最高工资即可
2.group by与where、having1). select FAge,count(*) from T_Employee where count(*)>1 group by FAge;--error: 2). select FAge,count(*) from T_Employee group by FAge having count(*)>1 --right:
1.2分析:
1)group by是按照某一个字段来对table中信息进行整合分组,在代码"group by简单语句"中,1、3正确,而2错误,可以做如下分析:
我们以FAge进行分组,即将每个年龄段的信息进行整合,获取到的结果可能是23岁的有abc三个人,工资为100、200、300,那么只能取其中某一个工资水平显示,如
max(FSalary),而不能直接写FSalary,否则计算机必然无法确定显示哪个而报错!参考下图,为“3)”的显示结果:
a). select FAge, count(*) from T_Employee where FSalary>2000 group by FAge; --rightb). select FAge, count(*) from T_Employee group by FAge having FSalary>2000; --error: c). select FAge, count(*) from T_Employee group by FAge having FAge>25; --right
分析:"a)"中使用where,可以实现对分组前的数据进行过滤;即如果25岁组中有3人(3000,2000,4000),那么使用where语句后,结果为25 2人。 而having只能对获得的组信息进行过滤,即对"select FAge, count(*) from T_Employee group by FAge"的结果进行过滤,此时无法再对FSalary去筛选,无法实现where实现的功能,因而"b)"错误; 但如果对年龄刷选having FAge>25就正确,因为select结果中包含FAge字段,可以进行简单where对年龄的筛选,即"c)"正确!
二.数据排序
2.1代码:
1). 单字段排序:select * from T_Employee order by FAge ASC; --对年龄进行排序2). 多字段排序select * from T_Employee order by FAge ASC ,Fsalary ASC; --进一步排序(年龄相同的,按照工资排序)!前面的优先级高!3). 对过滤后结果进行排序: select * from T_Employee where FAge>24 order by FAge DESC;
2.2 分析
1)排序条件可以有多个,但越前优先级越高;
2)ASC升序(default)、 DESC降序,平时使用排序时,ASC方式虽为默认,但最好不要省略,增强代码可读性
三.通配符
3.1代码
1). 通配符_,单个字符:select * from T_Employee where FName LIKE '_erry';2). 通配符%,零或多个字符:select * from T_Employee where FName like '%n%'; --FName中含有字母n的select * from T_Employee where FNumber like 'DEV%'; --FNumber中以DEV开始的
3.2分析
注意区分Like中 单字符 不定字符用法的区别!
四. 空值处理
4.1代码:
1)空值查询select 'abc'+'123' --结果:abc123select ''+'123' --结果:123select null+'123' --结果:NULLselect * from T_Employee where FName=null; --error 没有结果,可以尝试<null >null <>null均无结果!select * from T_Employee where FName is null; --right 查询到name为null的结果!select * from T_Employee where FName is not null; --error 查询到所有name不为null的结果! 2)空值的处理update T_Employee set FSubCompany=null,FDepartment='Sales'where FNumber='SALE004'; --设定某项为nullselect isnull(FSubCompany,'未知子公司') from T_Employee --字段为null的则显示默认值'未知子公司'
4.2分析: 1)数据库中,一个列如果没有指定值,那么值就是null,此处的null与c#中的null不同,数据库中表示“不知道”,而不是“没有” 2)可以尝试FName!=null、FName=<null、FName=>null、FName=<>null均无结果!五.限制结果集的行数
5.1 代码:
1). select top 5* from T_Employee --显示表中前五个2). select top 5* from T_Employee order by FSalary Desc --按照工资倒序排列,取前五个(最高的五个)
5.2 分析
top 5* 表示表中前五条信息,由升序或降序来决定顺序。
top 5 FSarray表示工资的前五个,由升序或降序来决定顺序。
六.子查询
6.1代码:
select top 5* from T_Employeewhere FNumber not in (select top 5 FNumber from T_Employee order by FSalary Desc)order by FSalary Desc;
6.2 分析
select语句嵌套,实现子查询,即在"select得到的表"中进行二次查询!
select top 5 FNumber from T_Employee order by FSalary Desc得到前五个数字;在此条件再用where查询!七. 去掉数据重复distinct 7.1代码
1). select FDepartment from T_Employee; --会有重复,完全显示查询结果2). select distinct FDepartment from T_Employee; --没有重复 3). select FDepartment,FSubCompany from T_Employee; --会有重复,完全显示查询结果 4). select distinct FDepartment,FSubCompany from T_Employee; --仅消除完全重复的行!
7.2 分析
参考,当table为下表时
执行"1)",完全显示出table的所有FDepartment 内容,字段会有重复,如红线标注区域;
执行"2)",显示FDepartment所有分类,没有重复
执行"3)",完全显示出table的所有FDepartment,FSubCompany 内容,会有重复,完全显示查询结果
执行"4)",仅消除完全重复的行。当FDepartment,FSubCompany数据完全相同时,数据合并,如的二个红色框中,会合并成一条数据,而第一个框中数据有差异不会合并,八. union与union all 8.1代码:
1). union会将重复数据合并9+6->14条!"表1中5条数据"+"表2中6条数据",结果可能小于11,因为将重复的FName字段合并select FName from TempEmployee !unionselect FName from T_Employee;2). union all不考虑重复数据,完全合并5+6->11条!!select FName from TempEmployee union allselect FName from T_Employee;3).其他关于union的示例(只要对应字段类型一致(不严谨)即可)select FName,FAge from TempEmployeeunionselect FName,FAge from T_Employee;select FName,FAge ,0 from TempEmployeeunionselect FName,FAge ,1 from T_Employee;select FName,FAge ,FIdCardNumber from TempEmployeeunionselect FName,FAge,FsubCompany from T_Employee;
--可用字段'临时工,无部门'补齐,实现union操作
select FIdCardNumber,FName,FAge ,'临时工,无部门' fromTempEmployee union select FNumber,FName,FAge,FDepartment from T_Employee;4).错误: select FName,FAge ,0 from TempEmployee --"0和FDepartment "类型不能转换union select FName,FAge,FDepartment from T_Employee;
8.2分析
1)union合并会消除重复项,而union all不考虑重复,会将数据完全合并累加
2) union时,需要两个table中要union的结果集中,具有相同的列数,且列类型要相容才可以union
3)union需要进行重复值扫描,效率比较低,所以如果不是确定要进行重复行合并,那么就使用union all
4)例子
a.在T_Employee中,(报表)查询员工最低年龄、最高年龄?参考:
select '正式员工最高年龄', max(FAge) from T_Employeeunion allselect '正式员工最低年龄', min(FAge) from T_Employeeunion allselect '临时工最高年龄', max(FAge) from TempEmployeeunion allselect '临时工最低年龄', min(FAge) from TempEmployee;
b.查询正式工信息:工号、工资,在最后一行加上员工工资额合计,参考:
select FNumber,FSalary from T_Employeeunion select '员工工资合计:',sum(FSalary) from T_Employee
(待续...)
附:创建的几个table
Table T_Employee
--创建表
drop table T_Employee
create table T_Employee(FNumber varchar(20),FName varchar(20),FAge int,FSalary numeric(10,2),PRIMARY KEY(FNumber));
insert into T_Employee(FNumber,FName,FAge,FSalary)values('DEV001','Tom',25,8300);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('DEV002','Jerry',28,4300);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('HR001','Jane',25,5300);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('HR002','Tina',26,6000);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('IT001','Smith',23,3300);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('IT002','Tom',27,9300);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('SALE001','John',24,3300);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('SALE002','Kerry',22,5500);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('SALE003','Stone',25,4500);
insert into T_Employee(FNumber,FName,FAge,FSalary)values('SALE004','john',23,5500);
--修改表:
alter table T_Employee add FSubCompany varchar(20); --分公司
alter table T_Employee add FDepartment varchar(20); --部门
alter table T_Employee add FInDate datetime; --入职日期
update T_Employee set FInDate='2010-09-07 12:00:00', FSubCompany='Beijing',FDepartment='development'where FNumber='DEV001';
update T_Employee set FInDate='2011-09-07 12:00:00', FSubCompany='Shenzhen',FDepartment='development'where FNumber='DEV002';
update T_Employee set FInDate='2012-09-07 12:00:00', FSubCompany='Beijing',FDepartment='HumanResource'where FNumber='HR001';
update T_Employee set FInDate='2009-09-07 12:00:00', FSubCompany='Beijing',FDepartment='HumanResource'where FNumber='HR002';
update T_Employee set FInDate='2010-09-07 12:00:00', FSubCompany='Shenzhen',FDepartment='InfoTech'where FNumber='IT001';
update T_Employee set FInDate='2011-09-07 12:00:00', FSubCompany='Beijing',FDepartment='InfoTech'where FNumber='IT002';
update T_Employee set FInDate='2011-09-07 12:00:00', FSubCompany='Beijing',FDepartment='Sales'where FNumber='SALE001';
update T_Employee set FInDate='2012-09-07 12:00:00', FSubCompany='Shenzhen',FDepartment='Sales'where FNumber='SALE002';
update T_Employee set FInDate='2009-09-07 12:00:00', FSubCompany='Beijing',FDepartment='Sales'where FNumber='SALE003';
update T_Employee set FInDate='2009-09-07 12:00:00', FSubCompany='Shenzhen',FDepartment='Sales'where FNumber='SALE004';
--创建Table TempEmployee
create table TempEmployee(FIdCardNumber varchar(20),FName varchar(20),FAge int not null);
insert into TempEmployee(FIdCardNumber,FName,FAge)values('12345601','张',20);
insert into TempEmployee(FIdCardNumber,FName,FAge)values('12345602','Tom',24);
insert into TempEmployee(FIdCardNumber,FName,FAge)values('12345603','李',18);
insert into TempEmployee(FIdCardNumber,FName,FAge)values('12345604','孙',26);
insert into TempEmployee(FIdCardNumber,FName,FAge)values('12345605','周',30);
insert into TempEmployee(FIdCardNumber,FName,FAge)values('12345606','武',27);
重点介绍数字函数、字符串函数、日期函数、类型转换函数cast /convert、表连接join等基础,并详细讲述一个综合练习,以巩固所学到的sql知识。
例子或代码中用到的table,若涉及到汇总(二)的表,如有需要请查阅汇总(二)!
一. 数字函数
1.1 代码
--绝对值:3.1415,3.1415select abs(3.1415); select abs(-3.1415);--舍入到最大整数:4,4,-3,-3select ceiling(3.2); select ceiling(3.7); select ceiling(-3.2); select ceiling(-3.7); --舍入到最小整数:3,3,-4,-4select floor(3.2); select floor(3.7); select floor(-3.2); select floor(-3.7);--四舍五入到“离半径最近的数”、后面的3即保留三位小数:3.1410,3.1420,-3.1410,-3.1420select round(3.1413,3); select round(3.1418,3);select round(-3.1413,3); select round(-3.1418,3);
二. 字符串函数
2.1 代码
select len('abcd'); --字符串长度:4select lower('abcdEMN'); --转小写select upper('abcdEMN'); --转大写select ltrim(' ss '); --去左空格select rtrim(' ss '); --去右空格select rtrim(ltrim(' ss ')); --去左右空格select substring('abcdef',3,3); --结果:cde;格式:substring(string,startIndex,length)2.2 分析
substring(string,startIndex,length),startIndex为子字符串在住字符串中的起始位置,length为子字符串的最大长度
三.日期函数
3.1代码
--当前时间select getdate();--分别获取年/月/日select DatePart(year,getdate()),DatePart(month,getdate()),DatePart(day,getdate());--在当前日期运算。格式:DateAdd(datePart,number,date);注:可用hh,dd,mm,yy表示hour/day/month/yearselect DateAdd(hour,3,getdate()); --加三小时hour select DateAdd(day,3,getdate()); --三天dayselect DateAdd(month,3,getdate()); --三月monthselect DateAdd(year,3,getdate()); --三年year--日期差值 格式:DateDiff(datePart,startDate,endDate); select DateDiff(hh,getdate(),DateAdd(dd,3,getdate()));3.2 分析
参考下面的例子
1)员工入职年限表select FName, FInDate as 入职日期,DateDiff(year,FInDate,getdate()) as 入职年限 from T_Employee;2)入职n年的人数统计表(FInDate为入职时间)select DateDiff(year,FInDate,getdate()) as 入职年限, count(*) as 入职人员数 from T_Employeegroup by DateDiff(year,FInDate,getdate())3)每年入职人数统计表select DatePart(year,FInDate),count(*) from T_Employeegroup by DatePart(year,FInDate)四. 类型转换
4.1代码
1) 类型转换两种方式:select cast('123'as int);select cast('2008-08-08' as datetime);select convert(datetime,'2009-09-09');select convert(varchar(50),123);2)注意:select '123'+1; --不好!有时会报错select cast('123' as int)+1; --最好转化为同一类型再运算select DatePart(year,'2008-8-8') --不好!有时会报错select DatePart(year,cast('2008-8-8' as datetime)) --最好转化为同一类型再运算4.2分析
注意使用cast、convert时候,需要考虑转换前后类型是否可以匹配,如要select cast('aaa'as int)是肯定会报错的
在"2)"中说到的情况还是应该注意,有些时候会出现错误!!!
五.case语句
5.1代码:
--case函数:实现离散值的判断select FName,(case FLevel --加上'判断值'when 1 then'普通客户'when 2 then'会员'when 3 then 'VIP'else '未知客户关系'end)as 客户类型from T_Customer--case函数:实现值域的判断select FName,FSalary,(case --不写'判断值'when FSalary<2000 then '低收入'when FSalary>=2000 and FSalary <5000 then '中收入'else '高收入'end)as 收入水平from T_Employee5.2 分析
1).上面方式分别针对离散值判断、值域判断;继续参考下面的小例子
2).有一张表记录了比赛成绩,创建代码已经给出(大家可以参考),根据该表,完成以下题目
--创建T_Scores
create table T_Scores(FName nvarchar(50) ,score nvarchar(50))insert into T_Scores(FName,score)values('Tom','胜'); insert into T_Scores(FName,score)values('Tom','负');insert into T_Scores(FName,score)values('Lucy','负');insert into T_Scores(FName,score)values('Lucy','负');insert into T_Scores(FName,score)values('Jerry','胜');eg_1.输出新表,如果胜为1,负为0
select FName,(case scorewhen N'胜' then 1else 0end)as '胜',(case scorewhen N'负' then 1else 0end)as '负'from T_Scoreseg_2.输出格式(统计胜负)
FName 胜 负
Tom 1 1
Lucy 0 2
Jerry 1 0select FName,sum( case score when N'胜' then 1 else 0 end)as '胜',sum( case score when N'负' then 1 else 0 end)as '负'from T_Scoresgroup by FName3).有一张表记录了正负值(正表示收入,负表示指出),创建代码已经给出(大家可以参考),根据该表,完成以下题目
--创建表 T_Ordercreate table T_Order(FNumber varchar(50) not null,FAmount int not null);insert into T_Order (FNumber,FAmount)values('Rk1',10);insert into T_Order (FNumber,FAmount)values('Rk2',20);insert into T_Order (FNumber,FAmount)values('Rk3',-30);insert into T_Order (FNumber,FAmount)values('Rk4',-10);统计出收支情况,字段为 单号 收入 支出
select FNumber as 单号,(case when FAmount>0 then FAmountelse 0end)as 收入,(case when FAmount<0 then abs(FAmount)else 0end)as 支出from T_Order六. 表连接join...on
6.1 代码:
--常规定义:select T_Orders.BillNo,T_Customers.namefrom T_Orders join T_Customers on T_Orders.CustomerId=T_Customers.Id;--可以定义如此,书写更简单:select o.BillNo as 订单号,c.Name,c.Agefrom T_Orders as o join T_Customers as c on o.CustomerId=c.Id;6.2分析
参考下方两个定义的表格,实现下方要求的操作。
--T_Customers表创建create table T_Customers( Id int not null, Name nvarchar(50) collate Chinese_PRC_CI_AS null, Age int null);insert into T_Customers(Id,Name,Age)values(1,N'tom',10);insert into T_Customers(Id,Name,Age)values(2,N'jerry',15);insert into T_Customers(Id,Name,Age)values(3,N'john',22);insert into T_Customers(Id,Name,Age)values(4,N'lily',18);insert into T_Customers(Id,Name,Age)values(5,N'lucy',18);--T_Orders表创建create table T_Orders( Id int not null, BillNo nvarchar(50) collate Chinese_PRC_CI_AS null, CustomerId int null --看做T_Customers表的外键(虽然实际关系并未建立)!);insert into T_Orders(Id,BillNo,CustomerId)values(1,N'001',1);insert into T_Orders(Id,BillNo,CustomerId)values(2,N'002',1);insert into T_Orders(Id,BillNo,CustomerId)values(3,N'003',3);insert into T_Orders(Id,BillNo,CustomerId)values(4,N'004',2);insert into T_Orders(Id,BillNo,CustomerId)values(5,N'005',2);insert into T_Orders(Id,BillNo,CustomerId)values(6,N'006',5);insert into T_Orders(Id,BillNo,CustomerId)values(7,N'007',4);insert into T_Orders(Id,BillNo,CustomerId)values(8,N'008',5);内容示意图
要求及答案:
--显示所有订单号对应的姓名、年龄:select o.BillNo as 订单号,c.Name,c.Agefrom T_Orders as o join T_Customers as c on o.CustomerId=c.Id;--显示15岁以上顾客对应的订单号、年龄、年龄:select o.BillNo,c.Name,c.Agefrom T_Orders as o join T_Customers as c on o.CustomerId=c.Idwhere c.Age>15--显示年龄大于‘平均年龄’的顾客的购买订单: (涉及子查询)select o.BillNo,c.Name,c.Agefrom T_Orders as o join T_Customers as c on o.CustomerId=c.Idwhere c.Age>(select avg(Age) from T_Customers)七.综合练习
创建一张表,记录电话呼叫员的工作流水号、呼叫员编号、对方号码、通话开始时间、通话结束时间、。
建表、插数据等要求自己写SQL语句来完成。
要求:
1)输出所有数据中通话时间最长的5条记录。(分析:需要用到orderby/ datediff。)
--所有通话时长(第一步)select *, DateDiff(second,StartDateTime,EndDateTime) as '通话时长'from T_Call;--通话时间最长的5条记录(第二步)select top 5* from T_Callorder by DateDiff(second,StartDateTime,EndDateTime) Desc;2)输出所有数据中拨打长途号码(以0开头)的总时长(分析:需要用到like/ datediff /sum。)
--获取长途号码(第一步)select * from T_Call where TelNum like '0%';--长途总时长:select'长途总时长:', sum(DateDiff(second,StartDateTime,EndDateTime))from T_Callwhere TelNum like '0%';3)输出本月通话时长最多的前三位呼叫员的编号(分析:datediff / DatePart / sum/order by)
****假定当前时间为'2010-7-20'--分辨是否为当月,0为是,其他为否(第一步)select CallerNumber, TelNum,DateDiff(month,StartDateTime,convert(datetime,'2010-7-20')) as '当前月'from T_Call--取当月的数据(第二步)select * from T_Callwhere DatePart(month,StartDateTime)=DatePart(month,'2010-7-20')--本月通话总时长最多的前3个呼叫员的编号(第三步)select top 3 CallerNumber from T_Callwhere DatePart(month,StartDateTime)=DatePart(month,'2010-7-20') group by CallerNumberorder by sum(DateDiff(second,StartDateTime,EndDateTime)) Desc --按照总量排序备注:当月判断也可使用 where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=04)输出本月拨打电话次数最多的前三位呼叫员的编号。(分析:group by/count/ order by)
--每个呼叫员拨打电话次数(第一步)select CallerNumber, count(*)from T_Callgroup by CallerNumber--当月(第二步)select CallerNumber, count(*)from T_Callwhere DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0group by CallerNumber--本月拨打次数最多的前3个呼叫员的编号(第三步)select top 3 CallerNumber, count(*)as '拨打次数'from T_Callwhere DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0group by CallerNumberorder by count(*) Desc5)输出所有数据的拨号流水,并且在最后一行添加总呼叫时长,即
呼叫员编号 对方号码 通话时长
... ... ...
汇总 市内号码总时长 长途号码总时长--通话时长(第一步)select Id,CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime)as '通话时长(s)'from T_Call--长途判断(第二步)select (casewhen TelNum like '0%' then 0else 1end)from T_Call--呼叫时长分析(第三步)select N'汇总',(case when TelNum not like '0%' then datediff(second,StartDateTime,EndDateTime)else 0end) as '市话',(case when TelNum like '0%' then datediff(second,StartDateTime,EndDateTime)else 0end) as '长途'from T_Call--呼叫员工编号/对方号码/通话时长 union all 通话时长(市话/长途)(第四步)select CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime) as '通话时长'from T_Callunion all--呼叫时长统计select N'汇总',convert(varchar(50),sum( --类型转换,否则溢出case when TelNum not like '0%' then datediff(second,StartDateTime,EndDateTime)else 0end)) as '市话',sum(case when TelNum like '0%' then datediff(second,StartDateTime,EndDateTime)else 0end) as '长途'from T_Call
- SQL语句之语法汇总
- SQL语句的基本语法 汇总,引用
- 超详细的SQL语句语法汇总
- SQL语法之查询语句
- SQL高级语法汇总
- SQL高级语法汇总
- sql 查询语法汇总
- Oracle常用语句语法汇总
- SQL查询语句汇总
- sql查询语句汇总
- SQL 语句汇总
- SQL语句优化汇总
- SQL语句优化汇总
- SQL语句优化汇总
- SQL语句优化汇总
- Sql语句汇总【2】
- SQL语句技巧汇总
- SQL语句优化汇总
- jQuery(function(){})与(function(){})(jQuery)的区别
- android布局管理的各种对齐问题
- 公众平台测试帐号开发全流程第7篇-用户管理
- C语言OJ项目参考(2399)求倒数和
- Otsu(大津法)源码级讲解
- SQL语句之语法汇总
- leetcode-m-Reorder List
- 公众平台测试帐号开发全流程第7篇-用户管理之用户分组管理
- 深入理解 Java 动态代理
- 测试使用
- HTML
- C语言OJ项目参考(1992)分数序列
- HTTP 599: SSL certificate problem: unable to get local issuer certificate错误
- leetcode的判断一个二叉树是否是平衡树