Sqlserver中一直于于是而每每为忽视的知识点一

已闹快2只月没更新博客了,实在是以近来出了无以复加多的作业,辞了工作,在湘雅医院要了一个几近月份,然后还要新换了工作……

在平时之劳作备受,Sqlserver中有的是知识点是时常用的,但是有时我们往往忽视了它,在过去底均等年里,一直以的凡Mysql,现在以开接触Sqlserver了,所以就算把一些常用又好忽略的Sqlserver知识点总结一点,以便备忘之用。

享有的操作都用依据Northwind数据库来进行操作。

在意:当以附加Northwind数据库的点子开展数据库迁移的当儿,可能会见起谬误:“Access
is denied”。解决方案: right-click the SQL Server  Management Studio icon and select 
Run
as administrator。

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

实践结果如下:

图片 1图片 2

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

履结果如下:

图片 3图片 4

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

会报错

图片 5

必须是:

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
    行计数表达式可以是拿回来整数值的外算术、常量或参数表达式。该行计数表达式不支持标量子查询图片 6图片 7

    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

和谐写的一个囤过程例子

图片 8图片 9

--------------------------------------创建闪电送存储过程-----------------------------------
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

View Code

demo2:分页存储过程

图片 10图片 11

------------------------------------------创建我的优惠券存储过程 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;

View Code

相关文章