sql Server学习篇

1.分组排序

select ActionID,Title, ParentID from xft_Actions
where ActionID in (select ActionID from xft_GroupRoles where GroupID = 2 and IsDel=0) group by ActionID ,ParentID,Title order by ParentID

2.left join的用法

之前只会用not in的写法 但是这个效率不够好，以下述not in转换left join为例

select * from BusinessCustomers
where Id not in
(select CustomerId from BusinessQQAccounts group by CustomerId having count(CustomerId)>=1)

select c.Id from BusinessCustomers c
left join
BusinessQQAccounts q
on c.Id=q.CustomerId
where QQ is null

 

在left join里右边的表如果和左边的表没有共同的结果集出现的均显示数据为null