ms-sql特殊查询及操作-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

将表进行分组后，查询每组的前 2 条记录

方案1：

select * from D_Student a where(
     select count (*) from D_Student b
             where b . FStuNo= a .FStuNo and  b .FCreateDate > a .FCreateDate
)<2 order by FStuNo

提示：如果表主键是使用int自增，那么下面语句也是一样效果的

select * from D_Student a where(
	select count (*) from D_Student b
	where b . FStuNo = a .FStuNo and  b .FId> a .FId
)<2 order by FStuNo

方案2：

select * from D_Student a where a.FID in(
	select top 2 FID from D_Student b where b .StuNo = a .StuNo
) order by StuNo

对比：第一种方案效率高

查询某班级下的所有学生的名字，返回结果如下面格式如：张三，李四，王五

select  STUFF ((SELECT ','+FName
                              FROM D_Student T1
                              left outer join D_Class B
                              on T1 .FClassId = B .FID where B .FID = '02FD43159C630'    FOR XML PATH ('')), 1,1 ,'')

分页查询

方案1：利用 row_number() over（order by 字段名）,实现每页20条记录

select * from (
     select * , row_number() over(order by FCreateDate ) as RowIndex
     from (
          select FID ,FName, FStuNO , FCreateDate
          from D_Student
     ) B where FID = '001'
) A
where RowIndex between 1 and 20

update 时创建别名做相关子查询

update D_Student   set FStuNO = (
     select FClassNo +"改"  from D_Student B
     left join D_Class C
      on B .FClassId = C .FID
     where B .FID = A .FID
) from D_Student A

一条语句实现增删改操作，通常用于同步两张表 ( mssql 2008)

merge into D_Student T --目标表
using (
                 select '001' as FId,'0000023' as FClassId,'张三' as FName,'2016-01-26' as FCreatDate,'59' as FStuNO
                                 union
                 select '002' as FId,'0000023' as FClassId,'李四' as FName,'2016-01-26' as FCreatDate,'76' as FStuNO
) S --源表
on T .FID = S . FID and T. FClassId = S. FClassId
when matched    --匹配即：当T表的FID 等于 S 的FID
then update set T.FName = S.FName , T . ClassId = S .ClassId
when not matched  --不匹配：当S表中有的 FID，但T 表中没有
then insert values( S. FId , S. FClassId,S .FName , S .FCreatDate ,S. FStuNO)
when not matched by source and(T .FClassId= '0000023')   --不匹配：T表中有的FID，但S表中没有
then delete ;

using：也可以是一张表。
注意：这边的delete 操作不当，可能会丢失整张表

转载于:https://my.oschina.net/u/2552286/blog/731570