几个SQL查询小技巧，你学会几个？

1、行列转换

问题：假设有张学生成绩表(tb)如下:

想变成(得到如下结果)：

代码：

WITH tb ( 姓名 , 课程 , 分数 )   AS   (   SELECT  N '张三'  , N '语文'  ,  74   UNION  ALL  SELECT  N '张三'  , N '数学'  ,  83   UNION  ALL  SELECT  N '张三'  , N '物理'  ,  93   UNION  ALL  SELECT  N '李四'  , N '语文'  ,  79   UNION  ALL  SELECT  N '李四'  , N '数学'  ,  86   UNION  ALL  SELECT  N '李四'  , N '物理'  ,  88   )   SELECT  姓名  ,  MAX ( CASE 课程 WHEN  '语文'  THEN 分数 ELSE  0  END )  语文 ,  MAX ( CASE 课程 WHEN  '数学'  THEN 分数 ELSE  0  END )  数学 ,  MAX ( CASE 课程 WHEN  '物理'  THEN 分数 ELSE  0  END )  物理  FROM  tb  GROUP   BY  姓名

2、分页

方案一：利用NOT IN和SELECT TOP分页语句形式。

 SELECT  TOP  10   *   FROM  TestTable  WHERE  ID  NOT   IN   (  SELECT  TOP  20  ID  FROM  TestTable  ORDER   BY  ID )   ORDER   BY  ID

方案二：利用ID大于多少和SELECT TOP分页语句形式。

 SELECT  TOP  10   *   FROM  TestTable  WHERE  ID  >   (   SELECT  MAX ( id )   FROM   (  SELECT  TOP  20  id  FROM  TestTable  ORDER   BY  id )   AS  T )   ORDER   BY  ID

方案三：利用SQL Server中的特性ROW_NUMBER进行分页 。

 SELECT   *   FROM   (   SELECT  ROW_NUMBER (  )  OVER (  ORDER   BY  ID  DESC  )   AS  ROWID ,  *   FROM  TestTable  )   AS  mytable  where  ROWID  between   21   and   40 

3、结果合并

合并重复行。

 SELECT   *   FROM  A  UNION   SELECT   *   FROM  B

不合并重复行。

 SELECT   *   FROM  A  UNION  ALL  SELECT   *   FROM  B

4、随机排序

 SELECT   *   FROM  TestTable  ORDER   BY  NEWID (  ) 

还可以结合TOP取随机的前N条记录。

 SELECT  TOP  100   *   FROM  TestTable  ORDER   BY  NEWID (  ) 

5、以任意符号分隔取两边数据

例如我们以逗号(,)来分割数据，将如下数据。

分割成如下图所示：

 SELECT  R ,  CASE WHEN  CHARINDEX (  ','  , R )  >  1  THEN  LEFT ( R , CHARINDEX (  ','  , R )  -  1  )  ELSE  NULL  END  AS  R1  ,  CASE WHEN CHARINDEX (  ','  , R )  >  1  THEN RIGHT ( R ,  ( LEN ( R )   -  CHARINDEX (  ','  , R )  )  )  ELSE  NULL  END  AS  R2  FROM  t

代码较长，我们对代码进行拆分来理解：

 SELECT  CHARINDEX (  ','  ,  ','  )   --结果是1   SELECT  CHARINDEX (  ','  ,  'NULL'  )   --结果是0   SELECT  CHARINDEX (  ','  ,  ''  )   --结果是0   SELECT  CHARINDEX (  ','  ,  'A,B'  )   --结果是2   SELECT  LEN (  'A,B'  )   --结果是3   SELECT  LEN (  'A,B'  )   -  CHARINDEX (  ','  ,  'A,B'  )   --结果是3-2=1   SELECT  RIGHT (  'A,B'  ,  (  LEN (  'A,B'  )   -  CHARINDEX (  ','  ,  'A,B'  )  )  )   --结果是 B 

最后一步我们将'A,B'拆分出来了B，同理A我们也可以用类似的方法获取到。

6、WAITFOR延时执行

例 等待1 小时2 分零3 秒后才执行SELECT 语句。

WAITFOR DELAY  '01:02:03'   SELECT   *   FROM  Employee

其中 DELAY是在延时多长时间后才开始执行。

例 等到晚上11 点零8 分后才执行SELECT 语句。

WAITFOR  TIME '23:08:00'   SELECT   *   FROM  Employee

其中TIME是等到具体某个时刻才开始执行。

原文地址：https://mp.weixin.qq.com/s/Xb4MIHnrRtIYFs5xZwhYtg

查看更多关于几个SQL查询小技巧，你学会几个？的详细内容...