sql知识整理(4)

Group
by Fage Having Count(*)>1

范围结果集行数

Select
top 3 * from T_Employee order by FSalary desc

检索依据薪金从高到低排序检索从第5名初阶一共三人的音信

select
top 3 * from T_Employee 

where
FNumber not in (select top 5 FNumber from T_Employee order by FSalary
desc)

order
by FSalary desc

sql
server二〇〇七后增添了Row_Number函数简化完毕,用Row_Number函数也能用来分页

select
*

from

(

select
*,row_number() over (order by salary desc) as row_num from
T_Employee

)as
T_EmployeeWithRowNumbers

where T_EmployeeWithRowNumbers.row_num
between 6 and 8

去掉数据重复

select
distinct FDepartment from T_Employee

distinct是对总体结果集进行数据重复处理的,而不是本着每多少个列

select
distinct FDepartment,FSubCompany  from
T_Employee(distinct是对准FDepartment,FSubCompany八个列,而不是一列)

Union(如果重复的数目会剔除掉,union
all不会去除重复数据)

select
FName,Fage from T_TempEmployee

union

select
FName,Fage from T_Employee

union因为要举办重复值扫描,所以效用低,因而一旦不是显然要统一重复行,那么就用union
all

数字函数

ABS():求相对值

CEILING():舍入到最大整数。3.33将被舍入为4,2.89将被舍入为3,-3.61将被舍入为-3

Ceiling->天花板

FLOO君越():舍入到细微整数。3.33将被舍入为3,2.89将被舍入为2,-3.61将被舍入为-4

Floor->地板

ROUND():四舍五入。

Round->“半径”。Round(3.1425,2)->3.14
后边的“2”指为保留四人精度

字符串函数

LEN():总结字符串长度

LOWER(),UPPER():转小写,大写

LTMuranoIM():字符串右侧的空格去掉

OdysseyT瑞虎IM():字符串左侧的空格去掉

LT索罗德IM(途锐TLacrosseIM()),字符串两边的空格去掉

SUBSTRING(string,start_position,length)

参数string为主字符串,start_position为子字符串在主字符串中开局地点,length为字符串的最大尺寸

日子函数

GETDATE():取得当今天卯时间

DATEADD(datepart,number,date),总结伸张今后的日子。参数date为待统计的日期,参数number为增量,参数datepart为计量单位,可选值见备注。DATEADD(DAY,3,date)为计算日期date的3天后的日子,而DATEADD(DAY,3,date)为总结日期date的3天后的日子,而DATEADD(MONTH,-8,date)为计算日期date的半年此前的日子

datepart可选值(year,quarter(季度),month,day,week,hh)

DATEDIFF(datepart,startdate,enddate):统计多少个日子之间的差额。datepart为计量单位,可选值参考datepart

eg:select
FName,FInDate,DateDiff(year,FIndate,getdate()) from T_Employee

DATEPA奥德赛T(datepart,date):重返多个日期的一定部分

select
DatePart(year,getdate())->借使是当年是二零一三年,则赶回2012

类型转换函数

CAST(expression
AS data_type) eg:select cast(‘123’ as int),cast(‘2008-08-08’ as
datetime)

CONVERT(date_type,expression)
eg:select convert(datetime,’2008-08-08′),convert(varchar(50),123)

SELECT
FldNumber

RIGHT(FldNumber,3)
as 后三位

CAST(翼虎IGHT(FldNumber,3)
AS INTEGE翼虎) as 后二位的整数格局

空值处理函数

ISNULL(expression,value):假设expression不为空则重回expression,否则再次回到value。eg
:select isnull(FName,’佚名’) as 姓名 from T_Employee

case函数用法1

单值判断,约等于switch
case

CASE
expression

WHEN
value1 THEN returnvalue1

WHEN
value2 THEN returnvalue2

WHEN
value3 THEN returnvalue3

ELSE
defaultreturnvalue

END

eg:

SELECT FName,

(

CASE FLevel 

WHEN 1 THEN ‘VIP客户’

WHEN 2 THEN ‘高级客户’

WHEN 3 THEN ‘日常客户’

ELSE ‘客户类型错误’

END

)as FLevelName

FROM T_Customer

select FName,

(

case

when FSalary<2000 then ‘低收入’

when FSalary>=3000 and FSalary<=伍仟 then ‘中等收入’

else ‘高收入’

end

) as 收入水平

from T_Employee

表中有A、B、C三列,用SQL语句完成:当A列大于B列时精选A列否则选拔B列,当B列大于C列时精选B列否则选用C列

select (case when a>b then a else b end),(case when b>c then b
else c end) from t

(

我的表结构如下 duiwu     shengfu1         胜2         胜1         胜1         负2         负1         胜2         负要查询出如下结果:duiwu   胜     负1       3      12       1      2就是分别统计出每支队伍的胜负次数,这样的sql语句该怎么写啊?大虾帮帮忙啊,谢谢了

最佳答案

select a.duiwu ,a.胜,b.负 from (select count(*) 胜, duiwu from tabb where shengfu='胜' group by duiwu ) a left join (select count(*) 负, duiwu from tabb where shengfu='负' group by duiwu ) b on a.duiwu=b.duiwu --sql2000调试通过,可以自己用case when改写一下

–sum后加括号,里面放case when

select duiwu ,
sum(case when shengfu = ‘胜’ then 1 else 0 end) win,
sum(case when shengfu = ‘负’ then 1 else 0 end) lost
from 表
group by duiwu ;

如若你用的是ACCESS数据库,可以用来下查询语句:
ACCESS,select duiwu,sum(iif(shengfu=”胜”,1,0)) as 胜,sum(iif(shengfu=”负”,1,0))
as 负 from 表名 group by duiwu 

)

ACCESS 1

表名:CallRecords
字段名:id,caller_id,telNum,starttime,endtime

1.select top 5 *
from CallRecords
order by datediff(second,starttime,endtime) desc

2.select sum(datediff(second,starttime,endtime))

from CallRecords
where telNum like ‘0%’

3.select top 3 caller_id

from CallRecords
where datediff(month,starttime,getdate())=0
 –或datepart(month,starttime)=datepart(month,getdate()) 应该也可以
group by caller_id
order by sum(datediff(second,starttime,endtime)) desc

4.select caller_id as 呼叫员编号,telNum as
对方号码,datediff(second,starttime,endtime) as 通话时长
from CallRecords
union all
select ‘汇总’,
sum(case when telNum like ‘0%’ then 0 else
datediff(second,starttime,endtime) end) as 市内号码总时长
sum(case when telNum like ‘0%’ then datediff(second,starttime,endtime)
else 0 end) as 市内号码总时长
from  CallRecords

索引

创立索引的方式,在表设计器中点击右键,选用“索引/键”-》添加-》在列中拔取索引包涵的列

行使索引能增高查询功效,可是索引也是占有空间的,而且丰盛、更新、删除数据的时候也须要共同更新索引,由此会下落insert、update、delete的进程。只在时时检索的字段上(一般为where)创建索引。

固然创制了目录,依然有只怕全表扫描,比如like、函数、类型转换等

相关文章