SQL 编程技巧

Ø  简介

本文主要介绍编写 SQL 时的一些编程技巧，方便有时候忘了便于查看，主要包含以下内容：

1.   SQL 语句中使用 +=、-=、*=、/= 运算符

2.   值为 NULL 的列或局部变量，参与计算的结果始终为 NULL

3.   将一个数值四舍五入至指定小数位

4.   删除重复记录，并保留一条记录

5.   使用 ROW_NUMBER() 生成序号并排序（支持多个排序字段）

 

1.   SQL 语句中使用 +=、-=、*=、/= 运算符

DECLARE @Num0 int=10, @Num1 int=10, @Num2 int=10, @Num3 int=10, @Num4 int=10;

SELECT @Num1 += @Num0, @Num2 -= @Num0, @Num3 *= @Num0, @Num4 /= @Num0;

SELECT @Num1 AS '+=', @Num2 AS '-=', @Num3 AS '*=', @Num4 AS '/=';

结果：

 

2.   值为 NULL 的列或局部变量，参与计算的结果始终为 NULL

DECLARE @Num1 int;

SET @Num1 = @Num1 + 55;

SELECT @Num1 AS Column1;

结果：

 

3.   将一个数值四舍五入至指定小数位，参考官方文档

SELECT ROUND(12.2646, 2) AS 四舍, ROUND(12.2656, 2) AS 五入, CAST(ROUND(12.2656, 2) AS numeric(19,2)) AS 设定小数位;

结果：

 

或者直接转换（推荐）：

SELECT CAST(1.1 AS decimal(18,2)) AS '填充小数位', CAST(1.124 AS decimal(18,2)) AS '四舍', CAST(1.125 AS decimal(18,2)) AS '五入';

结果：

 

4.   删除重复记录，并保留一条记录

工作中，有时候数据某些原因写入了相同的多条记录，此时需要编写 SQL 去除重复，但需要保留一条，下面是笔者想到的一种写法（欢迎讨论其他实现方法）。

1)   SQL 代码

DELETE CustomerVisitInfo

WHERE AuditState=1

AND VisitTime >= '2018-06-13 00:00:00' AND VisitTime <= '2018-06-13 23:59:59'

AND Id<>(

    SELECT MIN(Id) FROM CustomerVisitInfo AS T1

    WHERE 1=1

    AND T1.CustomerId=CustomerVisitInfo.CustomerId

    AND T1.SaleUserId=CustomerVisitInfo.SaleUserId

    AND T1.VisitDesc=CustomerVisitInfo.VisitDesc

    AND (DATEDIFF(SECOND, T1.VisitTime, CustomerVisitInfo.VisitTime)<20 OR DATEDIFF(SECOND, CustomerVisitInfo.VisitTime, T1.VisitTime)<20)

);

2)   说明

1.   首先，执行以上代码会删除相同记录中大于最小Id的记录（保留最小 Id 的记录）。

2.   这里使用 CustomerId、SaleUserId、VisitDesc 三个字段比较是否记录相同（可以根据需要比较）。

3.   使用 VisitTime 字段比较是否在相同时间（相差20秒）插入的记录。

 

5.   使用 ROW_NUMBER() 生成序号并排序（支持多个排序字段）

WITH ce1 (Id1, Id2, Name) AS (

    SELECT 3 AS Id1, 8 AS Id2, 'A' AS Name UNION

    SELECT 2 AS Id1, 7 AS Id2, 'B' AS Name UNION

    SELECT 4 AS Id1, 6 AS Id2, 'C' AS Name UNION

    SELECT 2 AS Id1, 5 AS Id2, 'D' AS Name UNION

    SELECT 3 AS Id1, 4 AS Id2, 'E' AS Name UNION

    SELECT 4 AS Id1, 3 AS Id2, 'F' AS Name UNION

    SELECT 3 AS Id1, 2 AS Id2, 'G' AS Name UNION

    SELECT 4 AS Id1, 1 AS Id2, 'H' AS Name

) SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Id1 ASC, Id2 DESC) AS RowNo, * FROM ce1) AS T

--ORDER BY T.RowNo ASC  --有时可能排序不正确，可以再通过 RowNo 排序