非典型UIsql

Over中Row_number用法：

select row_number()
over(partition by bldguid order by total)
as rowid,
roomguid,room
from p_room
order by bldguid

Avg()的partition by的用法

select avg(total)
over(partition by bldguid)
as avgtotal,
roomguid,room
from p_room

 注：分组后再进行order by：partition by bldguid order by total

with的用法：

--带列名的用法
with orders(order1,order2)
as
(select orderguid,buguid from s_order)
select *  from orders

 

--With实现递归
with proj1(id,code,fullcode)
as
(
select id,code,cast(code as varchar(200)) as fullcode
from proj where parentid is null
union all
select proj.id,proj.code,cast(proj1.fullcode+'.'+proj.code as varchar(200)) as fullcode
from proj  join proj1  on proj.parentid=proj1.id
)
select * from proj1
order by fullcode

 

--查询每个项目下房价最高的房间信息
select p_project.projguid,r.roomguid,r.total
from p_project 
outer apply (select top 2 roomguid,total
from p_room where p_room.projguid=
p_project.projguid order by total desc) as r
order by projguid

 

--传统的行转列：使用sum(case “”  then )  as “”
select userid,
sum(case subname when 'a' 
then score else 0 end) as a,
sum(case subname when 'b' 
then score else 0 end) as b,
sum(case subname when 'c' 
then score else 0 end) as c
from s_sub2user
group by userid

 

--透视行转列：
select userid,[a],[b],[c] from 
(select userid,score,subname from s_sub2user)
as s
pivot (sum(score) for subname in ([a],[b],[c]))
as s2

 注：表中s2是必须加的

 Insert into select(将select中的数据直接插入表格中)

 

insert into ins(buguid,value)
select buguid,value from mybusinessunit
cross join instem
where iscompany=1
and buguid<>'11B11DB4-E907-4F1F-8835-B9DAAB6E1F23'

 

 注：Select 结果集 into 表名 from 表(只能对不存在的表进行操作，不然回报错)

update select

 

--通过子查询
Update biao set 字段 from mybusinessunit where 条件(要求字段中需要唯一)
update bu1
set buname=(select top 1 buname from
mybusinessunit b where b.buguid=bu1.buguid)
update bu1 set bu1.buname=bu.buname
from mybusinessunit bu
where bu1.buguid=bu.buguid

 

 

 

 

 

转载于:https://www.cnblogs.com/elevenlang/p/3490722.html