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最大记录
-- 方法1
select * from @table t where not exists(select 1 from @table where name = t.name and value > t.value)
-- 方法2
select * 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 subNo
from @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 = 0
select @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返回