常用运算符和函数、case\top\over

------------------------------------------------------------------------------------------------
--排序规则
------------------------------------------------------------------------------------------------
Latin1_General_CI_AS 基于字典顺序对字符数据进行排序和比较(不区分大小写)默认
Latin1_General_CS_AS 基于字典顺序对字符数据进行排序和比较(区分大小写)


------------------------------------------------------------------------------------------------
--top()
------------------------------------------------------------------------------------------------
--top(5) with ties代表返回前5行+返回行中和最后一行相同的行
select top(5) with ties  orderid,orderdate,custid,empid
from Sales.Orders
order by orderdate desc, orderid desc


------------------------------------------------------------------------------------------------
--over()
------------------------------------------------------------------------------------------------
--over()对select阶段操作的所有行的总价格
--over(partition by custid)返回当前客户（和当前行具有相同custid的所有行）的总价格
select orderid,custid,val
,SUM(val) over() as totalvalue
,SUM(val) over(partition by custid) as custtotalvalue
from sales.OrderValues
--100.代表小数参加运算,计算cutid的价格占同custid总价格的百分比，以及占所有总和的百分比
select orderid,custid,val
,val*100./SUM(val)over() as pctall
,val*100./SUM(val)over(partition by custid) as pctcust
from Sales.OrderValues


--over()的四个排序函数
select orderid,custid,val
,ROW_NUMBER() over(order by val) as rownumber   --ROW_NUMBER()正常序号排序1 2 3 4 5 6...
,RANK() over(order by val) as ranks --RANK()相同数值排序一样，不连续1 2 3 3 5 6...
,DENSE_RANK() over(order by val) as denserank--DENSE_RANK()相同数值排序一样，并且连续1 2 3 3 4 5 6...
,NTILE(10) over(order by val) as ntiles --NTILE(10)表示将查出结果分10组显示
from Sales.OrderValues


--所有相同的custid，按照val排序，然后通过ROW_NUMBER()对其进行排名
--over()子句中指定order by逻辑与数据展示没有什么关系，不会改变查询结果值，不加就不能保证输出中行的任何顺序
select orderid,custid,val
,ROW_NUMBER() over(partition by custid order by val) as rownum
from Sales.OrderValues
order by custid,val


------------------------------------------------------------------------------------------------
--小数点精度计算(数字与字符串转换)
------------------------------------------------------------------------------------------------
cast(coll as numeric(12,2)--数据类型的精度为12，带有2位小数点


------------------------------------------------------------------------------------------------
--运算符优先级
------------------------------------------------------------------------------------------------
/*
()
* / % Multiply,Division,Modulo
+ -
= > < >= <= <>  != !< !>
not
and
or  in  between
= Assignment
*/


------------------------------------------------------------------------------------------------
--case表达式
------------------------------------------------------------------------------------------------
/*
--相当于一个三元表达式或C#中的选择结构
--语法1：
case 
when 字段表达式 then 结果
……
else 结果
end
--语法1：
case 字段
when 值 then 结果
……
else 结果
end
*/


--查询时将字段为空的值排在最后【重要】
order by 
case when stuId is null then 1 else 0 end,stuId

--除非商品的种类非常少，而且也是静态的，否则不要使用，建议单独一张表建立商品名称，用连结join操作
select productid,productname,categoryid,
case categoryid
when 1 then 'Beverages'
when 2 then 'Condiments'
when 3 then 'Confections'
when 4 then 'Dairy Products'
when 5 then 'rains/Cereals'
when 6 then 'Meat/Poultry'
when 7 then 'Produce'
else 'Unknown Category'
end as categoryname
from Production.Products


--when后面不仅可以相等性比较，还可以通过表达式返回结果
select orderid,custid,val,
case 
when val <1000.00then'Less then 1000'
when val between 1000.00 and 3000.00then'Between 1000 and 3000'
when val >3000then'More then 3000'
else 'Unknown'
end as valuecategory
from sales.OrderValues


--先判断col1不等于0，在判断col2/col1>2，最后当值是Yes的时候才返回，如果是No的话不返回
select col1,col2
from dbo.T1
where
case
when col1=0 then 'No'
when col2/col1>2then 'Yes'
else 'No'
end ='Yes';
--等价于
select col1,col2
from dbo.T1
where col1<>0 and col2>col1*2;


------------------------------------------------------------------------------------------------
--运算符和函数
------------------------------------------------------------------------------------------------
--用+串联几个字段，当有一个字段值为空时，结果也为空
select custid,country,region,city,
country+N','+region+N','+city as result
from Sales.Customers
--修改方法：先将以下设置为off就可以了,但是一般不建议这样做
set concat_null_yields_null off;
set concat_null_yields_null on;


--T-SQL提供了一套字符串处理函数
substring(string,start,length)--提取从指定位置开始，具有特定长度的子字符串
left(string,n)  right(string,n)--要从字符串的左边或右边提取的字符个数
len(string) datalength(string)--获取字符长度
charindex(substring,string[,start_pos])--在第二个参数中搜索第一个参数
patindex(pattern,string)--在字符串中找到第一次出现数字的位置
replace(string,substring1,substring2)--将字符串中出现的所有某个子串替换为另一个字符串
replicate(string,n)--将字符串复制多少次
stuff(string,pos,delete_length,insertstring)--从输入参数pos指定的位置开始删除delete_length参数指定长度的字符，然后将insertstring参数指定的字符串插入到pos指定的位置
upper(string)   lower(string)--字符转换为大小写
rtrim(string) ltrim(string)--用于删除输入字符串中的尾随空格和前导空格




------------------------------------------------------------------------------------------------
--构造函数，可以虚拟建表
------------------------------------------------------------------------------------------------
select *,AVG(stuScore) over(partition by stuclass) as avgScore 
,sum(stuScore) over()as sumavgscore
from 
(
values
(1,'张三','1班',100)
,(2,'李四','1班',79)
,(3,'汪汪','1班',40)
,(4,'体昂','2班',88) 
,(5,'做鹅','2班',78)
) as tbl(studId,stuName,stuClass,stuScore)