SQL语句总结

1.union 和union all 操作符

 1）union内部的select语句必须拥有相同的列，列也必须有相似的数字类型。同时，每条select语句中列的顺序相同。

 union语法（结果集无重复）

select x from table1union select y from table2

 union all语法（结果集有重复）

select x from table1union allselect y from table2

 

 2.CTE(Common Table Expression)

  公共表表达式，可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE ⅥEW 语句的执行范围内定义的临时结果集

   CTE可用于：

  1).创建递归查询

  2).在同一语句中多次生成的表

 把test表中salary最大的id记录保存在test_CTE中，再调用

with test_CTE(id,salary)　　as　　（　　　　select id,max(salary)　　　　from test　　　　group by id　　 ）　　select * from test_cte

 查询ItemId=2及子节点，也就是管理费用和其下属所有节点的信息

Declare @i intselect @i=2;with Co_ItemNameSet_CTE(ItemId,ParentItemId,ItemName,[Level])AS(  select ItemId, ParentItemId,ItemName,1 AS [Level]   from Co_ItemNameSet  where  ItemId=@i  union all  select c.ItemId,c.ParentItemId,c.ItemName,[Level]+1  from Co_ItemNameSet c inner join Co_ItemNameSet_CTE ct  on c.ParentItemId=ct.ItemId)select * from Co_ItemNameSet_CTE

结果：

3.row_number() over()

--不用partition byselect name,Gender,FenShu, row_number()over(order by FenShu desc) as num  from dbo.PeopleInfo--使用partition byselect name,Gender,FenShu, row_number()over(partition by Gender order by FenShu desc) as num  from dbo.PeopleInfo;--查找出不同性别中分数最高的学生with tempas(  select name,Gender,FenShu, row_number()over(partition by Gender order by FenShu desc) as num  from dbo.PeopleInfo)select * from temp where num=1

4.Join

 从两个或更多表中获取结果，就要执行Join

  Inner Join

 

select P.LastName,P.FirstName, O.OrderNo from dbo.Persons as Pinner join dbo.mOrder as Oon P.Id= O.Id_Porder by P.LastName

 结果：
 

 left Join

select c.customerid as 消费者,COUNT(O.[orderid]) as  订单数  from [SQLDemo].[dbo].[Customers] as C left join [SQLDemo].[dbo].[Orders] as O   on C.customerid=O.customerid  where  C.city='Madrid'    group by C.customerid  having COUNT(O.orderid)<3  order by 订单数

5.SQL通配符

/****** SQL 通配符 ******/select * from Persons where City like '[ALN]%'