非典型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