SqlServer常用语法总结

前言

    近期公司做一个短信平台，写了一些关于统计方面的存储过程，今天刚好有空总结一下。

统计查询和性能提升

一、使用WITH AS提高性能简化嵌套SQL

    首先，感谢@飞洋过海和@宋沄剑，通过阅读他们的博客让我对WITH AS进行了系统的学习。下面贴出他们文章地址，供大家参考。

    1、http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html

    2、http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html

    然后，进入正题。

    第一个统计：对用户发单量做一个统计，需要对U_UserAccount（用户表）和U_Order_Base（订单表）进行连接查询。

    第一种方式：通过子查询，也能实现，后来老大看了代码说：最好把子查询拆解出来查询，这样性能会有所提升而且后期维护也比较方便，于是乎给我简单介绍了关于WITH AS公用表表达式（Common Table Expression)的用法，然后就有了第二种实现方式。

SELECT 
    AgentID,
    NickName,
    Mobile,
    SUM(OrderCount) AS OrderCount
FROM(
    SELECT
        account.AgentID,
        ISNULL(account.NickName,'') AS NickName,
        account.Mobile,
        COUNT(orders.OrderID) AS OrderCount  --累计成交订单
    FROM T_PaoTui.dbo.U_UserAccount AS account WITH(NOLOCK)
    LEFT JOIN T_PaoTui.dbo.U_Order_Base AS orders WITH(NOLOCK) ON orders.Userid=account.Id
    WHERE orders.UserType=0 AND orders.[State]=10   --普通用户
        --代理商
        AND ((@AgentID=-999) OR (account.AgentID=@AgentID))
       --发单日期
        AND ((@BeginTime IS NULL) OR (FORMAT(orders.AddTime,'yyyy-MM-dd')>=@BeginTime))
       AND ((@EndTime IS NULL) OR (FORMAT(orders.AddTime,'yyyy-MM-dd')<=@EndTime))
    GROUP BY account.AgentID,account.NickName,account.Mobile
) AS MainTable
GROUP BY AgentID,Mobile,NickName
HAVING ((@BeginOrder IS NULL) OR (SUM(OrderCount)>=@BeginOrder))  --成交订单量
   AND ((@EndOrder IS NULL) OR (SUM(OrderCount)<=@EndOrder))

    第二种方式：通过WITH AS实现，但是放到正式库之后查询性能并不是那么理想，7万多条数据竟然查询了10秒，经过分析原来是日期条件出了问题，思考片刻后觉得是因为通过字符串比对的原因，心想数字比较应该比字符串比较效率要高吧，结果用了DATEDIFF(DAY,orders.AddTime,@BeginTime)函数转换成INT就行比较，果然如此，查询只用了不到1秒的时间，于是就有了第三种实现方式。

WITH
OrderBase AS 
(
    SELECT 
        orders.Userid,
        orders.AddTime
    FROM T_PaoTui.dbo.U_Order_Base AS orders WITH(NOLOCK)
    WHERE orders.UserType=0 AND orders.[State]=10
        --代理商
        AND ((@AgentID=-999) OR (orders.AgentID=@AgentID))
        --发单日期
        AND ((@BeginTime IS NULL) OR (FORMAT(orders.AddTime,'yyyy-MM-dd')>=@BeginTime))
        AND ((@EndTime IS NULL) OR (FORMAT(orders.AddTime,'yyyy-MM-dd')<=@EndTime))
),
Account AS (
    SELECT
        account.AgentID,
        ISNULL(account.NickName,'') AS NickName,
        account.Mobile,
        COUNT(0) AS OrderCount  --成交订单量
    FROM T_PaoTui.dbo.U_UserAccount AS account WITH(NOLOCK)
    INNER JOIN OrderBase AS orders WITH(NOLOCK) ON orders.Userid=account.Id
    WHERE ((@AgentID=-999) OR (account.AgentID=@AgentID))  --代理商
    GROUP BY account.AgentID,account.NickName,account.Mobile
    HAVING ((@BeginOrder IS NULL) OR (COUNT(0)>=@BeginOrder))  --成交订单量
       AND ((@EndOrder IS NULL) OR (COUNT(0)<=@EndOrder))
)
SELECT
    AgentID,
    NickName,
    Mobile,
    SUM(OrderCount) AS OrderCount
FROM Account
GROUP BY AgentID,Mobile,NickName

    第三种方式：把日期比较(FORMAT(orders.AddTime,'yyyy-MM-dd')>=@BeginTime))换成DATEDIFF(DAY,orders.AddTime,@BeginTime)提升查询速度。

WITH
OrderBase AS 
(
    SELECT 
        orders.Userid,
        orders.AddTime
    FROM T_PaoTui.dbo.U_Order_Base AS orders WITH(NOLOCK)
    WHERE orders.UserType=0 AND orders.[State]=10
        --代理商
        AND ((@AgentID=-999) OR (orders.AgentID=@AgentID))
        --发单日期
        AND ((@BeginTime IS NULL) OR (DATEDIFF(DAY,orders.AddTime,@BeginTime)<=0))
        AND ((@EndTime IS NULL) OR (DATEDIFF(DAY,orders.AddTime,@EndTime)>=0))
),
Account AS (
    SELECT
        account.AgentID,
        ISNULL(account.NickName,'') AS NickName,
        account.Mobile,
        COUNT(0) AS OrderCount  --成交订单量
    FROM T_PaoTui.dbo.U_UserAccount AS account WITH(NOLOCK)
    INNER JOIN OrderBase AS orders WITH(NOLOCK) ON orders.Userid=account.Id
    WHERE ((@AgentID=-999) OR (account.AgentID=@AgentID))  --代理商
    GROUP BY account.AgentID,account.NickName,account.Mobile
    HAVING ((@BeginOrder IS NULL) OR (COUNT(0)>=@BeginOrder))  --成交订单量
       AND ((@EndOrder IS NULL) OR (COUNT(0)<=@EndOrder))
)
SELECT
    AgentID,
    NickName,
    Mobile,
    SUM(OrderCount) AS OrderCount
FROM Account
GROUP BY AgentID,Mobile,NickName

事务

一、利用事务进行短信充值和消费并对短信库存进行更新。

    废话不多说，直接上代码，有不足的地方请大家多多指点。

ALTER PROCEDURE [dbo].[UUPRO_SMS_Recharge]
    @AgentID INT,
    @SpendType INT,
    @SpendCount INT,
    @UserID INT,
    @out_msg VARCHAR(200) OUTPUT,
    @out_result INT OUTPUT  -- 1：成功；2：当前可使用短信数小于查询人数；3：出现异常
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET @out_msg = '操作成功。';
    SET @out_result = 1;
    DECLARE @StockID INT,
            @TotalCount INT,
            @BeforeCount INT,
            @AfterCount INT
    -- Insert statements for procedure here
    BEGIN TRY
        BEGIN TRANSACTION    --开始事务
        SET @StockID = (SELECT ID FROM S_MsgStock WHERE AgentID = @AgentID);
        SET @TotalCount = (SELECT TotalCount FROM S_MsgStock WHERE AgentID = @AgentID);
        IF @SpendType = 1    --短信充值记录
        BEGIN
            IF @StockID > 0
            BEGIN
                SET @BeforeCount = @TotalCount;
                SET @AfterCount = @TotalCount + @SpendCount;
                --更新代理商可使用短信数
                UPDATE S_MsgStock SET TotalCount = @AfterCount WHERE AgentID = @AgentID;
                SET @out_result = @@ROWCOUNT;
                --添加充值日志
                INSERT INTO T_PaoTui_log.dbo.S_SpendLog(AgentID,MsgStockID,SpendCount,SpendType,BeforeCount,AfterCount,UserID)
                VALUES(@AgentID,@StockID,@SpendCount,@SpendType,@BeforeCount,@AfterCount,@UserID);
            END
            ELSE
            BEGIN
                --添加代理商可使用短信数
                INSERT INTO S_MsgStock(AgentID,TotalCount)VALUES(@AgentID,@SpendCount);
                --添加充值日志
                SET @StockID = @@IDENTITY;
                SET @out_result = @@ROWCOUNT;
                INSERT INTO T_PaoTui_log.dbo.S_SpendLog(AgentID,MsgStockID,SpendCount,SpendType,BeforeCount,AfterCount,UserID)
                VALUES(@AgentID,@StockID,@SpendCount,@SpendType,0,@SpendCount,@UserID);
            END
        END
        ELSE IF @SpendType = 2    --短信消费记录
        BEGIN
            SET @BeforeCount = @TotalCount;
            SET @AfterCount = @TotalCount - @SpendCount;
            IF @AfterCount < 0
            BEGIN
                SET @out_msg = '可使用短信数不够，请充值后再试。';
                SET @out_result = 2;
            END
            ELSE
            BEGIN
                --更新代理商可使用短信数
                UPDATE S_MsgStock SET TotalCount = @AfterCount WHERE AgentID = @AgentID
                SET @out_result = @@ROWCOUNT
                --添加消费日志
                INSERT INTO T_PaoTui_log.dbo.S_SpendLog(AgentID,MsgStockID,SpendCount,SpendType,BeforeCount,AfterCount,UserID)
                VALUES(@AgentID,@StockID,@SpendCount,@SpendType,@BeforeCount,@AfterCount,@UserID)
            END
        END
        COMMIT TRANSACTION    --提交事务
    END TRY
    BEGIN CATCH
        SET @out_msg = '短信充值/扣除失败，请稍后再试。'
        SET @out_result = 3;
        /*SELECT ERROR_NUMBER() AS ErrorNumber,  --错误代码
               ERROR_SEVERITY() AS ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
               ERROR_STATE() AS ErrorState,  --错误状态码
               ERROR_PROCEDURE() AS ErrorProcedure, --出现错误的存储过程或触发器的名称。
               ERROR_LINE() AS ErrorLine,  --发生错误的行号
               ERROR_MESSAGE() AS ErrorMessage  --错误的具体信息*/
        ROLLBACK TRANSACTION    --回滚事务
    END CATCH
END

存储过程分页

一、利用WITH AS做一个简单的分页

DECLARE @pageCurrent AS INT=2, 
@pageSize AS INT=10;

WITH 
OrderBase AS
(
    SELECT *,ROW_NUMBER() OVER(ORDER BY OrderID) AS RowNumber FROM dbo.U_Order_Base
)
SELECT * FROM OrderBase 
WHERE OrderBase.RowNumber BETWEEN (@pageCurrent-1)*@pageSize+1 AND @pageCurrent * @pageSize

通用的分页可以参考@光头毅博客http://www.cnblogs.com/EasonWu/archive/2012/09/21/2697326.html

二、利用OFFSET FETCH分页

DECLARE @pageIndex INT=1,
        @pageSize INT=10;
SELECT
    * 
FROM dbo.M_Merchant
ORDER BY ID DESC
OFFSET (@pageIndex -1) * @pageSize ROWS 
FETCH NEXT @pageSize ROWS ONLY;

使用SQL语句创建表、修改表、添加列

一、创建表

CREATE TABLE UserInfo
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   Name VARCHAR(20),
   Age INT,
   Address VARCHAR(50)
)

二、修改表

1、表重命名

EXEC sp_rename 'Users','UserInfo'

2、添加列

ALTER TABLE dbo.UserInfo
ADD Age SMALLINT NOT NULL DEFAULT(0)

3、修改列

ALTER TABLE dbo.UserInfo
ALTER COLUMN Age INT NOT NULL

4、删除列

ALTER TABLE dbo.UserInfo
DROP COLUMN Age

三、复制表

1、复制整张表

SELECT * INTO New_UserInfo from UserInfo

2、复制表结构

SELECT * INTO New_UserInfo from UserInfo where 1=2

3、复制表内容

INSERT INTO New_UserInfo select * from UserInfo

四、循环修改表

DECLARE @i INT,
        @sql VARCHAR(2000)
SET @i=1
WHILE @i<=1
BEGIN
    --添加列
    SET @sql = CONCAT('ALTER TABLE dbo.U_UserOrder_',CAST(@i AS VARCHAR),
                ' ADD Shipments INT NOT NULL DEFAULT 0,',
                ' Remarks varchar(500) NOT NULL DEFAULT ('''')');
    EXEC(@sql);

    --EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发货次数', 
    --        @level0type=NSCHEMA,@level0name=Ndbo, @level1type='NTABLE', 
    --        @level1name=@tableName1, @level2type='NCOLUMN',@level2name=N'Shipments'

    --EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'物品备注' ,
    --        @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
    --        @level1name=@tableName1, @level2type=N'COLUMN',@level2name=N'Remarks'

    SET @i=@i+1;
END

添加/修改/删除/查询

一、添加

二、修改

1、使用Inner Join跨表更新数据

UPDATE m SET m.AddTime=ua.AddTime
FROM dbo.M_Merchant AS m
INNER JOIN T_paotui..U_UserAccount AS ua
ON ua.Id=m.PaoTuiUserID

三、删除

四、查询

1、使用Not Exists提高查询性能，是Not IN查询速度的3倍

SELECT 
    CityID,ID,CompanyName 
FROM M_Merchant AS m
WHERE NOT EXISTS
      (
        SELECT 
            MerchantID 
        FROM Paotui_Crm..M_PublicMerchant AS pm 
        WHERE pm.MerchantID=m.ID
      )

2、联表查询

SELECT 
    ISNULL(SUM(mdr.OrderOkNum),0) AS NewOrderOkNum,
    mdr.ReportDate
FROM M_MerchantDailyReport AS mdr,
    (SELECT 
        mdr.PaoTuiUserID,
        MIN(mdr.ReportDate) AS ReportDate
    FROM M_MerchantDailyReport AS mdr
    WHERE mdr.SalerID=5199 
        AND mdr.OrderOkNum>0
        AND DATEDIFF(DAY,mdr.ReportDate,GETDATE())<=30
    GROUP BY mdr.PaoTuiUserID) AS m
WHERE mdr.PaoTuiUserID=m.PaoTuiUserID
    AND DATEDIFF(DAY,mdr.ReportDate,GETDATE())=1
GROUP BY mdr.ReportDate

3、排名函数DENSE_RANK()和RANK()

DECLARE @Student TABLE
(
    ID INT,
    Name VARCHAR(50),
    Score INT
)

INSERT INTO @Student
SELECT 1,'黄一',99 UNION ALL 
SELECT 2,'吴二',96 UNION ALL
SELECT 3,'张三',91 UNION ALL
SELECT 4,'李四',98 UNION ALL
SELECT 5,'王五',97 UNION ALL
SELECT 6,'赵六',100 UNION ALL
SELECT 7,'田七',95 UNION ALL
SELECT 8,'纪八',100 UNION ALL
SELECT 9,'邱九',93 UNION ALL
SELECT 10,'林十',98
--DENSE_RANK连续排序，有两个第一名时仍然跟着第二名
SELECT 
    DENSE_RANK() OVER(ORDER BY Score DESC) AS ranks,* 
FROM @Student
--RANK跳跃排序，有两个第一名时接下来就是第三名
SELECT 
    RANK() OVER(ORDER BY Score DESC) AS ranks,* 
FROM @Student

--ROW_NUMBER
SELECT *,ROW_NUMBER() OVER(PARTITION BY Class ORDER BY Score DESC) FROM @Student

--SUM和PARTITION BY
SELECT *,SUM(Score) OVER(PARTITION BY Class) FROM @Student

由于时间原因先写到这里，未完待续。。。