SQL基本操作二

 

-- Update Delete ---left join 
Update TableA SET TableA.tName ='AA'  FROM TableA A LEFT JOIN TableB   B ON A.TID=B.TID

Delete TableA from TableA A LEFT JOIN TableB   B ON A.TID=B.TID

--索引 
--索引占据磁盘空间
--top 用法 row_number
select top 2 * from TableB where tid not in (select top 2 tid  from TableB)
select * from (select tid,tname,row_number() over(order by tid) as num from TableB) M where num between 3 and 5

--row_number  根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号
ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)


-- Union 去掉重复项  Union all 不去掉重复项

--With 语句
 with STable  as  
(
 select tid,tname from TableB where tname='ad6'
)
 select * from TableB  where TableB.tid=6


-- 开窗函数 count(*) 子查询 over()

select tid,tname,
(
select count(*) from TableB where tid between 1 and 3
)
num
from TableB

select tid,tname,count(*) from TableB group by tid,tname
select tid,tname,count(*) over() from TableB where  tid between 1 and 3
--partition by 独立于结果集
select tid,tname,count(*) over(partition by tname) from TableB
-- 多个开窗函数
select tid,tname,count(*) over(partition by tname),count(*) over(partition by tid) from TableB