SQL常用技巧总结

不多说，看sql代码注释（mssql）

-- ============== 1、随机数 ======================-- (1)随机取出10条数据select top 10 * from tablename order by newid()-- (2)随机唯一标识select newid()-- ============== 2、取最大值记录 ==================declare  @table table(id int,name varchar(50),value int)insert into @table  select 1,'A',1 union all  select 2,'A',2 union all  select 3,'B',3 union all  select 4,'B',5 union all  select 5,'C',2 union all  select 6,'C',3 union all  select 7,'C',8-- 取出每种值对应的总数select t.*,t2.total from @table t left join(select value,COUNT(value) total from @table group by value) t2 on t2.value = t.value-- (1)取每组中某字段最大值的记录:每个Name对应Value最大记录-- 方法1select * from @table t where not exists(select 1 from @table where name = t.name and value > t.value)-- 方法2select * from @table t where value=(select max(value) from @table t2 where t2.name=t.name) order by id-- (2)取每组中某字段最大值的记录（如果存在多个，取其中一条）select * from @table t where id = (select top 1 id from @table t2 where t2.name = t.name order by value DESC)-- (2)问题:每个Name,每天对应的最后一次登陆日期?-- ============== 3、行号问题 ==================-- (1)取出分组中行号-- 方法1：语句嵌套select t.*,[SubRowNum] = 1 + (select COUNT(1) from @table t1 where t1.name = t.name and t1.value < t.value) from @table t-- 方法2：行函数select t.*,[SubRowNum] = row_number() over(partition by name order by id) from @table t---- (2)组号、分组行号select t.id,t.name, t.value,    ROW_NUMBER() over(order by id) rownum,    (select COUNT(distinct name) from @table t1 where t1.name <= t.name) nameNo,    row_number() over(partition by name order by id) as subNofrom @table t-- 【总结】根据以上示例，涉及“参数穿层”写法的SQL性能低，但是远比Join灵活度高！-- ============== 4、重复数据处理 ==================-- (1)取不重复数据select distinct name from @table-- (2)只取指定字段重复值的第一条select * from @table where id in(select MIN(id) from @table group by name)-- (3)删除指定字段值重复记录delete @table where id not in(select MIN(id) from @table group by name)-- ============== 5、分页 ==========================-- 输入条件：起始编号，页大小，排序字段，查询条件declare @begin int = 1,@pageSize int = 10,@total int = 0select @total = COUNT(*) from sms_biz_UserInfo --[where ...]-- (1)方法一：使用Top/* select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段*/select top (@pageSize) * from (select top (@total - @begin + 1) EmployeeID,Name from sms_biz_UserInfo order by Name desc) t,    sms_biz_UserInfo t2 where t2.EmployeeID = t.EmployeeID order by t.Name-- (2)方法二：Row_Number函数select * from(select t.*,ROW_NUMBER() over(order by t.Name) as RowNum     from sms_biz_UserInfo t) as t where t.RowNum between @begin and @begin + @pageSize - 1-- ============== 6、数据的组合与分隔 ==================declare @user table(Id int,DeptId int,Name varchar(50),Info varchar(50))insert into @user(Id,Name,DeptId)     select 1,'Zhang San',1 union all    select 2,'Li Si',1 union all    select 3,'Wang Wu',1 union all    select 4,'Zhang BaoCai',2 union all    select 5,'Liu Wei',2 union all    select 4,'Liu Meng',3 union all    select 4,'Mei Biao',3 declare @dept table(DeptId int,DeptName varchar(50))insert into @dept    select 1,'AAAA' union all    select 2,'BBBB' union all    select 3,'CCCC' union all    select 4,'DDDD'  -- (1)将多行数据用","组合成一条数据    select t.*,        STUFF((select ',' + Name from @user as u where u.DeptId = t.DeptId for xml path('')),1,1,'') as Names    from @dept as t -- (2)将用","组合的字符串分隔成多行数据 -- 思路:创建Table-valued Functions将一个字符串分隔的以Table返回