天天看點
傳回

Sqlserver中一直在用又經常被忽略的知識點一

已經有快2個月沒有更新部落格了，實在是因為最近發生了太多的事情，辭了工作，在湘雅醫院待了一個多月，然後又新換了工作......

在平時的工作中，Sqlserver中許多知識點是經常用到的，但是有時候我們往往忽略了它們，在過去的一年裡，一直使用的是Mysql，現在又開始接觸Sqlserver了，是以就把一些常用又容易忽視的Sqlserver知識點總結一點，以便備忘之用。

所有的操作都将基于Northwind資料庫來進行操作。

注意：當采用附加Northwind資料庫的方式進行資料庫遷移的時候，可能會出現錯誤：“Access is denied”。解決方案： right-click the SQL Server  Management Studio icon and select  Run as administrator。

資料線上Mock位址：https://www.mockaroo.com/

SET NOCOUNT ON 介紹

在存儲過程中，經常用到SET NOCOUNT ON；

作用：阻止在結果集中傳回顯示受T-SQL語句或則usp影響的行計數資訊。

當SET NOCOUNT ON 時候，不傳回計數，當SET NOCOUNT OFF時候，傳回計數。

當SET NOCOUNT ON 時候，會更新@@RowCount，但是不向用戶端發送存儲過程每個語句的DONE_IN_proc消息。

如果存儲過程中包含一些并不傳回實際資料的語句，使用SET NOCOUNT ON時，網絡通信流量便會大量減少，可以顯著提高應用程式性能。

SET NOCOUNT 指定的設定隻在執行或運作時候生效，分析時候不生效。

示例：

USE Northwind
GO
SET NOCOUNT OFF;
SELECT TOP 5 OrderDate FROM Orders
GO

執行結果如下：

Sqlserver中一直在用又經常被忽略的知識點一
Sqlserver中一直在用又經常被忽略的知識點一
USE Northwind
GO
SET NOCOUNT ON;
SELECT TOP 5 OrderDate FROM Orders
GO
Sqlserver中一直在用又經常被忽略的知識點一
Sqlserver中一直在用又經常被忽略的知識點一

Go 介紹

如果隻是執行一條語句，有沒有GO都一樣。

如果多條語句之間用GO分隔開就不一樣了。

每個被GO分隔的語句都是一個單獨的事務，一個語句執行失敗不會影響其它語句執行。

GO 不是 Transact-SQL 語句；而是可為  SQL Server 查詢分析器識别的指令。

如果你的SQL過長的時候，就要寫GO，或者有一些語句，它隻能是第一句操作的，在之前你也得寫 GO ，GO的意思是分批處理語句，有加這個 GO ，就執行GO 行的代碼，執行後再執行接下來的代碼。

像以下這種情況下就要用到GO ，以達到分批處理資料的目的，否則将會報錯。

IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_OrderInfo')
DROP VIEW View_OrderInfo
create view View_OrderInfo
as
select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID=c.CustomerID

會報錯

Sqlserver中一直在用又經常被忽略的知識點一

必須是：

IF EXISTS(SELECT 1 FROM sys.views WHERE name='View_OrderInfo')
DROP VIEW View_OrderInfo
GO
create view View_OrderInfo
as
select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID=c.CustomerID

select count(*)  count(1) count(2) count('a') 之間的差別

count(*)：找表中最短的列進行統計行數

count(1) count(2) count('a')：對常數列進行統計行數。它們的執行方式是一樣的，沒有任何差別。

很顯然采用count(1) count(2) count('a')的方式，效率會更高，因為count(*)會先去算出最短的列，然後再去統計。雖然現在的Sqlserver查詢分析器自動會幫我們做一些優化，但是我們必須知道它們的實作原理。

WITH (NOLOCK)

缺點:

　　1.會産生髒讀

　　2.隻适用于select查詢語句

優點:

　　1.有些文章說，加了WITH (NOLOCK)的SQL查詢效率可以增加33%。

　　2.可以用于inner join 或者left join等語句

髒讀: 一個使用者對一個資源做了修改，此時另外一個使用者正好讀取了這條被修改的記錄，然後，第一個使用者放棄修改，資料回到修改之前，這兩個不同的結果就是髒讀。

詳細内容:

　　要提升SQL的查詢效能，一般來說大家首先會考慮建立索引(index)。其實除了index的建立之外，當我們在下SQL Command時，在文法中加一段WITH (NOLOCK)可以改善線上大量查詢的環境中資料集被LOCK的現象藉此改善查詢的效能。

不過有一點千萬要注意的就是，WITH (NOLOCK)的SQL SELECT有可能會造成Dirty Read(髒讀)。

例如：

SELECT o.OrderID,o.OrderDate,o.Freight,d.Quantity,d.UnitPrice
FROM [dbo].[Orders] o WITH (NOLOCK)
JOIN [dbo].[Order Details] d WITH (NOLOCK)
ON o.OrderID=d.OrderID

DELETE、INSERT、UPDATE這些需要transaction的指令就不能使用WITH (NOLOCK)。

加了WITH (NOLOCK)即告訴SQL Server，我們的這段SELECT指令無需去考慮目前table的transaction lock狀态，是以效能上會有明顯的提升，而且資料庫系統的Lock現象會有明顯的減少(包含Dead Lock)。

當使用NoLock時，它允許閱讀那些已經修改但是還沒有交易完成的資料。是以如果有需要考慮transaction事務資料的實時完整性時，使用WITH (NOLOCK)就要好好考慮一下。

如果不需考慮transaction，WITH (NOLOCK)或許是個好用的參考。

Sqlserver高效分頁

在SQLserver2012之前，分頁我們一般是使用ROW_NUMBER()。以擷取第11條到第50條資料為例：

ROW_NUMBER()的實作方式：

select * from (
　　　　select *, ROW_NUMBER() OVER(Order by a.OrderID DESC ) AS RowNumber from Orders as a
　　) as b
　　where RowNumber BETWEEN 11 and 50

SQLserver2012中OFFSET & FETCH實作方式：

SELECT o.* FROM dbo.Orders o
ORDER BY o.OrderID DESC OFFSET 10 ROWS FETCH NEXT 40 ROWS ONLY

OFFSET & FETCH實作方式 更加精簡更加高效。

使用 OFFSET-FETCH 中的限制

  • ORDER BY 是使用 OFFSET 和 FETCH 子句所必需的。
  • OFFSET 子句必須與 FETCH 一起使用。永遠不能使用 ORDER BY … FETCH。
  • TOP 不能在同一個查詢表達式中與 OFFSET 和 FETCH 一起使用。
  • OFFSET/FETCH 行計數表達式可以是将傳回整數值的任何算術、常量或參數表達式。該行計數表達式不支援标量子查詢
    Sqlserver中一直在用又經常被忽略的知識點一
    Sqlserver中一直在用又經常被忽略的知識點一
    ALTER PROCEDURE getLightningSendOverTimeOrder --閃電送到期存儲過程
(@OverTimeMinutes INT,--逾時時間門檻值
@GetLasterDay int --取最近多少天的訂單
)
As
DECLARE @sql NVARCHAR(MAX);
BEGIN
set nocount on;
set @Sql=
N'WITH    OrderInfo
          --查詢近一天的，處理逾時的閃電送訂單
          AS ( SELECT   ID ,
                        CommunityId ,
                        SubOrderNumber
               FROM     dbo.[Order] WITH ( NOLOCK )
               WHERE    DATEDIFF(day, CreatedOn, GETDATE()) <= @GetLasterDay
                        AND DATEDIFF(mi, CreatedOn, ModifiedOn) > @OverTimeMinutes
                        AND LightningSend = 1
                        AND IsDeleted = 0
 --待付款(商品訂單),待确認(商品訂單),待發貨(商品訂單),待收貨(商品訂單)
                        AND OrderStatusId IN (
                        ''C2EE784F-F29B-4E18-8D73-761264339005'',
                        ''FA1A31DF-3855-41BB-9F5C-A09F3AB4C408'',
                        ''ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5'',
                        ''15F06407-C82C-4CA8-984E-37FB3BD9963C'' )
             ),
       SmsOrder
          --沒有發送過資訊的訂單
          AS ( SELECT   o.ID ,
                        o.CommunityId ,
                        o.SubOrderNumber
               FROM     OrderInfo o WITH ( NOLOCK )
                        where not EXISTS(SELECT 1 FROM  [dbo].[Sms_LightningSendOrderOverTime] lso
                        WITH ( NOLOCK ) WHERE o.SubOrderNumber = lso.SubOrderNumber)                      
             ),
        SmsOrderInfo
          AS ( SELECT   
                        so.CommunityId ,
                        so.SubOrderNumber ,
                        p.PublisherId ,
                        c.ConvenienceStoreID ,
                        oi.ProductId ,
                        p.CategoryId ,
                        p.ProcessType
               FROM     SmsOrder AS so WITH ( NOLOCK )
                        INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                        INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                        INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                              AND p.LightningSend = 1
                                                               AND p.ProcessType != 1 -- 便利店
             ),
            MerchantSmsOrderInfo
          AS ( SELECT   
                        so.CommunityId ,
                        so.SubOrderNumber ,
                        p.PublisherId ,
                        c.ConvenienceStoreID ,
                        oi.ProductId ,
                        p.CategoryId ,
                        p.ProcessType
               FROM     SmsOrder AS so WITH ( NOLOCK )
                        INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                        INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                        INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                              AND p.LightningSend = 1
                                                              AND p.ProcessType = 1 -- 商家
             ),
        ResultInfo
        AS ( 
    SELECT   usr.Id AS UserId ,
                        soi.SubOrderNumber ,
                        usr.PhoneNumber
               FROM     SmsOrderInfo AS soi
                        INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                        INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                              AND org.Levels = 2 --便利店
                        INNER JOIN OrderDistributaryCategoryRelation AS odcr
                        WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                           AND odcr.CategoryType = 1
                                           AND odcr.SellerId = soi.ConvenienceStoreID
                                           AND odcr.CategoryId = soi.CategoryId
                        INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                              AND odb.SellerId = soi.ConvenienceStoreID
                        INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON usr.Id = odbr.MerchantUserId
                                                              AND odbr.MerchantId = soi.ConvenienceStoreID
               UNION ALL
               SELECT   u.Id AS UserId ,
                        soi.SubOrderNumber ,
                        u.PhoneNumber
               FROM     dbo.[User] u WITH ( NOLOCK )
                        INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
                                                            AND odb.IsDefault = 1
                        INNER JOIN SmsOrderInfo soi WITH ( NOLOCK ) ON odb.SellerId = soi.ConvenienceStoreID
             ------------------------------商家-----------------------------
               UNION ALL
               SELECT   usr.Id AS UserId ,
                        soi.SubOrderNumber ,
                        usr.PhoneNumber
               FROM     MerchantSmsOrderInfo AS soi
                        INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                        INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                              AND org.Levels = 1 --商家
                        INNER JOIN OrderDistributaryCategoryRelation AS odcr
                        WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                           AND odcr.CategoryType = 1
                                           AND odcr.CategoryId = soi.CategoryId
                        INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                              AND odb.SellerId = soi.PublisherId
                        INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON odcr.MerchantUserId = odbr.MerchantUserId
                                                              AND odbr.MerchantId = soi.ConvenienceStoreID
               UNION ALL
               SELECT   u.Id AS UserId ,
                        soi.SubOrderNumber ,
                        u.PhoneNumber
               FROM     MerchantSmsOrderInfo soi WITH ( NOLOCK )
                        INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON odb.SellerId = soi.PublisherId
                                                            AND odb.IsDefault = 1
                                                            AND odb.IsDeleted = 0
                        INNER JOIN dbo.[User] u WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
             )
            SELECT  r.SubOrderNumber ,
            r.UserId,
            r.PhoneNumber
    FROM    ResultInfo r '
exec sp_executesql @Sql,N'@OverTimeMinutes int,@GetLasterDay int',@OverTimeMinutes,@GetLasterDay;
SET NOCOUNT OFF;
END
    View Code

自己寫的一個存儲過程例子

Sqlserver中一直在用又經常被忽略的知識點一
Sqlserver中一直在用又經常被忽略的知識點一
--------------------------------------建立閃電送存儲過程-----------------------------------
IF EXISTS(Select Name From Sysobjects Where Name='getLightningSendOverTimeOrder' And Xtype='P')
DROP PROCEDURE [dbo].getLightningSendOverTimeOrder
GO
CREATE PROCEDURE getLightningSendOverTimeOrder --閃電送到期存儲過程
(@OverTimeMinutes INT,--逾時時間門檻值
@GetLasterDay int --取最近多少天的訂單
)
As
DECLARE @sql NVARCHAR(MAX);
BEGIN
set nocount on;
set @Sql=
N'WITH    OrderInfo
          --查詢近一天的，處理逾時的閃電送訂單
          AS ( SELECT   ID ,
                        CommunityId ,
                        SubOrderNumber
               FROM     dbo.[Order] WITH ( NOLOCK )
               WHERE    DATEDIFF(day, CreatedOn, GETDATE()) <= @GetLasterDay
                        AND DATEDIFF(mi, CreatedOn, ModifiedOn) > @OverTimeMinutes
                        AND LightningSend = 1
                        AND IsDeleted = 0
 --待付款(商品訂單),待确認(商品訂單),待發貨(商品訂單),待收貨(商品訂單)
                        AND OrderStatusId IN (
                        ''C2EE784F-F29B-4E18-8D73-761264339005'',
                        ''FA1A31DF-3855-41BB-9F5C-A09F3AB4C408'',
                        ''ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5'',
                        ''15F06407-C82C-4CA8-984E-37FB3BD9963C'' )
            UNION ALL
--狀态一直是待支付狀态的
SELECT ID ,
                        CommunityId ,
                        SubOrderNumber
               FROM     dbo.[Order] WITH ( NOLOCK )
               WHERE    DATEDIFF(day, CreatedOn, GETDATE()) <=@GetLasterDay
                        AND LightningSend = 1
                        AND IsDeleted = 0
                        AND (OrderStatusId =''ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5'' AND DATEDIFF(mi, CreatedOn, GETDATE()) >@OverTimeMinutes)
             ),
       SmsOrder
          --需要發送過資訊的訂單
          AS ( SELECT   o.ID ,
                        o.CommunityId ,
                        o.SubOrderNumber
               FROM     OrderInfo o WITH ( NOLOCK )
                        where not EXISTS(SELECT 1 FROM  [dbo].[Sms_LightningSendOrderOverTime] lso
                        WITH ( NOLOCK ) WHERE o.SubOrderNumber = lso.SubOrderNumber)                      
             ),
        SmsOrderInfo
          AS ( SELECT   
                        so.CommunityId ,
                        so.SubOrderNumber ,
                        p.PublisherId ,
                        c.ConvenienceStoreID ,
                        oi.ProductId ,
                        p.CategoryId ,
                        p.ProcessType
               FROM     SmsOrder AS so WITH ( NOLOCK )
                        INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                        INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                        INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                              AND p.LightningSend = 1
                                                               AND p.ProcessType != 1 -- 便利店
             ),
            MerchantSmsOrderInfo
          AS ( SELECT   
                        so.CommunityId ,
                        so.SubOrderNumber ,
                        p.PublisherId ,
                        c.ConvenienceStoreID ,
                        oi.ProductId ,
                        p.CategoryId ,
                        p.ProcessType
               FROM     SmsOrder AS so WITH ( NOLOCK )
                        INNER JOIN Community c WITH ( NOLOCK ) ON so.CommunityId = c.ID
                        INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID = oi.OrderId
                        INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId = p.Id
                                                              AND p.LightningSend = 1
                                                              AND p.ProcessType = 1 -- 商家
             ),
        ResultInfo
        AS ( 
    SELECT   usr.Id AS UserId ,
                        soi.SubOrderNumber ,
                        usr.PhoneNumber
               FROM     SmsOrderInfo AS soi
                        INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                        INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                              AND org.Levels = 2 --便利店
                        INNER JOIN OrderDistributaryCategoryRelation AS odcr
                        WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                           AND odcr.CategoryType = 1
                                           AND odcr.SellerId = soi.ConvenienceStoreID
                                           AND odcr.CategoryId = soi.CategoryId
                        INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                              AND odb.SellerId = soi.ConvenienceStoreID
                        INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON usr.Id = odbr.MerchantUserId
                                                              AND odbr.MerchantId = soi.ConvenienceStoreID
               UNION ALL
               SELECT   u.Id AS UserId ,
                        soi.SubOrderNumber ,
                        u.PhoneNumber
               FROM     dbo.[User] u WITH ( NOLOCK )
                        INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
                                                            AND odb.IsDefault = 1
                        INNER JOIN SmsOrderInfo soi WITH ( NOLOCK ) ON odb.SellerId = soi.ConvenienceStoreID
             ------------------------------商家-----------------------------
               UNION ALL
               SELECT   usr.Id AS UserId ,
                        soi.SubOrderNumber ,
                        usr.PhoneNumber
               FROM     MerchantSmsOrderInfo AS soi
                        INNER JOIN dbo.[User] AS usr WITH ( NOLOCK ) ON soi.ConvenienceStoreID = usr.OrgId
                        INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId = org.Id
                                                              AND org.Levels = 1 --商家
                        INNER JOIN OrderDistributaryCategoryRelation AS odcr
                        WITH ( NOLOCK ) ON usr.Id = odcr.MerchantUserId
                                           AND odcr.CategoryType = 1
                                           AND odcr.CategoryId = soi.CategoryId
                        INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id = odb.MerchantUserId
                                                              AND odb.SellerId = soi.PublisherId
                        INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON odcr.MerchantUserId = odbr.MerchantUserId
                                                              AND odbr.MerchantId = soi.ConvenienceStoreID
               UNION ALL
               SELECT   u.Id AS UserId ,
                        soi.SubOrderNumber ,
                        u.PhoneNumber
               FROM     MerchantSmsOrderInfo soi WITH ( NOLOCK )
                        INNER JOIN OrderDistributary odb WITH ( NOLOCK ) ON odb.SellerId = soi.PublisherId
                                                            AND odb.IsDefault = 1
                                                            AND odb.IsDeleted = 0
                        INNER JOIN dbo.[User] u WITH ( NOLOCK ) ON u.Id = odb.MerchantUserId
             )
            SELECT  r.SubOrderNumber ,
            r.UserId,
            r.PhoneNumber
    FROM    ResultInfo r '
exec sp_executesql @Sql,N'@OverTimeMinutes int,@GetLasterDay int',@OverTimeMinutes,@GetLasterDay;
SET NOCOUNT OFF;
END


GO

demo2:分頁存儲過程

Sqlserver中一直在用又經常被忽略的知識點一
Sqlserver中一直在用又經常被忽略的知識點一
------------------------------------------建立我的優惠券存儲過程 created by zouqj-2017-3-1-----------------------------------
IF EXISTS(Select Name From Sysobjects Where Name='usp_GetMyCouponsList' And Xtype='P')
DROP PROCEDURE usp_GetMyCouponsList
GO
CREATE PROC usp_GetMyCouponsList
(
@PageIndex int, --頁碼
@PageSize int, --
@hostName nvarchar(100),--伺服器位址
@MemberID UNIQUEIDENTIFIER, --會員ID
@IsGetTotalInfo BIT,  --是否得到統計資訊，1為得到統計資訊，0為不得到統計資訊，傳回記錄集
@HasStatus BIT, -- 是否有狀态
@CouponsStatus INT --優惠券狀态
)
AS
----------------------------------------定義參數--------------------------------------
declare @strSql NVARCHAR(max);
DECLARE @IsUseCount INT
DECLARE @ExpiredCount INT
DECLARE @NoUseCount INT
DECLARE @where NVARCHAR(100);
set nocount on;
----------------------------------------SQL開始--------------------------------------
IF @HasStatus=1
BEGIN
SET @where='m.[State]=@CouponsStatus';
END
ELSE
BEGIN
SET @where='1=1'
END
IF @IsGetTotalInfo=0
BEGIN
SET @strSql=N'SELECT DISTINCT t.* FROM (SELECT m.[State] AS [STATUS],
m.CouponCode AS CouponsCode,
m.CampaignID AS CampaignID,
c.Code as CampaignCode,
c.Name AS CampaignName,
b.ValidityStartTime AS CampaignStartTime,
b.ValidityEndTime AS CampaignEndTime,
c.Price as CouponsAmount,
(CASE WHEN c.IsLimited =1 THEN 1 ELSE 2 END  ) as IsLimited,
c.FullAmount as MinAmount,
e.[Type] AS CouponsType,
(CASE WHEN b.IconUrl IS NULL THEN N'''' WHEN b.IconUrl=N'''' THEN N'''' ELSE @hostName+b.IconUrl END) AS IconUrl

FROM  Loy_MemberCoupon m WITH ( NOLOCK ) INNER JOIN 
 [dbo].[MK_Campaign] AS a WITH ( NOLOCK ) ON m.CampaignID=a.ID AND m.MemberID=@MemberID AND '+@where+'
        LEFT OUTER JOIN [dbo].[MK_CouponConfig] AS b  WITH ( NOLOCK ) ON a.[ID] = b.[CampaignID] 
        LEFT OUTER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] = c.[ID]
        LEFT OUTER JOIN [dbo].[MK_CouponRestriction] AS d  WITH ( NOLOCK ) ON c.[ID] = d.[CompainID]
        LEFT OUTER JOIN [dbo].[MK_CouponRestrictCategory] AS e WITH ( NOLOCK ) ON d.[ID] = e.[CouponRestrictionID]
        ) t
        ORDER BY t.CampaignEndTime
offset (@PageIndex-1)*@PageSize  ROWS  FETCH NEXT @PageIndex*@PageSize ROWS ONLY'
exec sp_executesql @strSql,N'@PageIndex int,@PageSize int,@hostName nvarchar(100),@MemberID UNIQUEIDENTIFIER,@where NVARCHAR(100),@CouponsStatus int',@PageIndex=@PageIndex,@PageSize=@PageSize,@hostName=@hostName,@MemberID=@MemberID,@where=@where,@CouponsStatus=@CouponsStatus
END
ELSE
BEGIN
-------------------------------------------------------------
SELECT @IsUseCount=COUNT(1) FROM (SELECT DISTINCT m.CouponCode
FROM  Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID=@MemberID AND m.[State]=2
        ) t
SELECT @ExpiredCount=COUNT(1) FROM (SELECT DISTINCT m.CouponCode
FROM  Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID=@MemberID AND m.[State]=3
        ) t
SELECT @NoUseCount=COUNT(1) FROM (SELECT DISTINCT m.CouponCode
FROM  Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID=@MemberID AND m.[State]=1
        ) t
SELECT @IsUseCount AS IsUseCount,@ExpiredCount AS ExpiredCount,@NoUseCount AS NoUseCount
END
----------------------------------------SQL結束--------------------------------------
set nocount off;
部落格位址： http://www.cnblogs.com/jiekzou/
部落格版權：

本文以學習、研究和分享為主，歡迎轉載，但必須在文章頁面明顯位置給出原文連接配接。

如果文中有不妥或者錯誤的地方還望高手的你指出，以免誤人子弟。如果覺得本文對你有所幫助不如【推薦】一下！如果你有更好的建議，不如留言一起讨論，共同進步！

再次感謝您耐心的讀完本篇文章。
其它：

.net-QQ群4：612347965

java-QQ群：805741535

H5-QQ群：773766020

我的拙作《ASP.NET MVC企業級實戰》《H5+移動應用實戰開發》

《Vue.js 2.x實踐指南》

《JavaScript實用教程 》

《Node+MongoDB+React 項目實戰開發》

已經出版，希望大家多多支援！
Sqlserver中一直在用又經常被忽略的知識點一
上一篇: Android Studio設定忽略檔案
下一篇: jQuery插件開發 - 其實很簡單