ampsqlserver中什么意思_Sqlserver中一直在用又经常被忽略的知识点一

已经有快2个月没有更新博客了&＃xff0c;实在是因为最近发生了太多的事情&＃xff0c;辞了工作&＃xff0c;在湘雅医院待了一个多月&＃xff0c;然后又新换了工作......

在平时的工作中&＃xff0c;Sqlserver中许多知识点是经常用到的&＃xff0c;但是有时候我们往往忽略了它们&＃xff0c;在过去的一年里&＃xff0c;一直使用的是Mysql&＃xff0c;现在又开始接触Sqlserver了&＃xff0c;所以就把一些常用又容易忽视的Sqlserver知识点总结一点&＃xff0c;以便备忘之用。

所有的操作都将基于Northwind数据库来进行操作。

注意&＃xff1a;当采用附加Northwind数据库的方式进行数据库迁移的时候&＃xff0c;可能会出现错误&＃xff1a;“Access is denied”。解决方案&＃xff1a;right-clicktheSQL Server  Management StudioiconandselectRun as administrator。

SET NOCOUNT ON 介绍

在存储过程中&＃xff0c;经常用到SET NOCOUNT ON&＃xff1b;

作用&＃xff1a;阻止在结果集中返回显示受T-SQL语句或则usp影响的行计数信息。

当SET NOCOUNT ON 时候&＃xff0c;不返回计数&＃xff0c;当SET NOCOUNT OFF时候&＃xff0c;返回计数。

当SET NOCOUNT ON 时候&＃xff0c;会更新&＃64;&＃64;RowCount&＃xff0c;但是不向客户端发送存储过程每个语句的DONE_IN_proc消息。

如果存储过程中包含一些并不返回实际数据的语句&＃xff0c;使用SET NOCOUNT ON时&＃xff0c;网络通信流量便会大量减少&＃xff0c;可以显著提高应用程序性能。

SET NOCOUNT 指定的设置只在执行或运行时候生效&＃xff0c;分析时候不生效。

示例&＃xff1a;

USENorthwindGO

SET NOCOUNT OFF;SELECT TOP 5 OrderDate FROMOrdersGO

执行结果如下&＃xff1a;

USENorthwindGO

SET NOCOUNT ON;SELECT TOP 5 OrderDate FROMOrdersGO

执行结果如下&＃xff1a;

Go 介绍

如果只是执行一条语句&＃xff0c;有没有GO都一样。

如果多条语句之间用GO分隔开就不一样了。

每个被GO分隔的语句都是一个单独的事务&＃xff0c;一个语句执行失败不会影响其它语句执行。

GO 不是 Transact-SQL 语句&＃xff1b;而是可为  SQL Server 查询分析器识别的命令。

如果你的SQL过长的时候&＃xff0c;就要写GO&＃xff0c;或者有一些语句&＃xff0c;它只能是第一句操作的&＃xff0c;在之前你也得写 GO &＃xff0c;GO的意思是分批处理语句&＃xff0c;有加这个 GO &＃xff0c;就执行GO 行的代码&＃xff0c;执行后再执行接下来的代码。

像以下这种情况下就要用到GO &＃xff0c;以达到分批处理数据的目的&＃xff0c;否则将会报错。

IF EXISTS(SELECT 1 FROM sys.views WHERE name&＃61;&＃39;View_OrderInfo&＃39;)DROP VIEWView_OrderInfocreate viewView_OrderInfoas

select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID&＃61;c.CustomerID

会报错

必须是&＃xff1a;

IF EXISTS(SELECT 1 FROM sys.views WHERE name&＃61;&＃39;View_OrderInfo&＃39;)DROP VIEWView_OrderInfoGO

create viewView_OrderInfoas

select c.ContactName,c.CompanyName,o.OrderDate,o.ShipName,o.OrderID,o.Freight from [Orders] o inner join Customers c on o.CustomerID&＃61;c.CustomerID

select count(*)  count(1) count(2) count(&＃39;a&＃39;) 之间的区别

count(*)&＃xff1a;找表中最短的列进行统计行数

count(1) count(2) count(&＃39;a&＃39;)&＃xff1a;对常数列进行统计行数。它们的执行方式是一样的&＃xff0c;没有任何区别。

很显然采用count(1) count(2) count(&＃39;a&＃39;)的方式&＃xff0c;效率会更高&＃xff0c;因为count(*)会先去算出最短的列&＃xff0c;然后再去统计。虽然现在的Sqlserver查询分析器自动会帮我们做一些优化&＃xff0c;但是我们必须知道它们的实现原理。

WITH (NOLOCK)

缺点:

1.会产生脏读

2.只适用于select查询语句

优点:

1.有些文章说&＃xff0c;加了WITH (NOLOCK)的SQL查询效率可以增加33%。

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

脏读: 一个用户对一个资源做了修改&＃xff0c;此时另外一个用户正好读取了这条被修改的记录&＃xff0c;然后&＃xff0c;第一个用户放弃修改&＃xff0c;数据回到修改之前&＃xff0c;这两个不同的结果就是脏读。

详细内容:

要提升SQL的查询效能&＃xff0c;一般来说大家首先会考虑建立索引(index)。其实除了index的建立之外&＃xff0c;当我们在下SQL Command时&＃xff0c;在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能。

不过有一点千万要注意的就是&＃xff0c;WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read(脏读)。

例如&＃xff1a;

SELECTo.OrderID,o.OrderDate,o.Freight,d.Quantity,d.UnitPriceFROM [dbo].[Orders] o WITH(NOLOCK)JOIN [dbo].[Order Details] d WITH(NOLOCK)ON o.OrderID&＃61;d.OrderID

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

加了WITH (NOLOCK)即告诉SQL Server&＃xff0c;我们的这段SELECT指令无需去考虑目前table的transaction lock状态&＃xff0c;因此效能上会有明显的提升&＃xff0c;而且数据库系统的Lock现象会有明显的减少(包含Dead Lock)。

当使用NoLock时&＃xff0c;它允许阅读那些已经修改但是还没有交易完成的数据。因此如果有需要考虑transaction事务数据的实时完整性时&＃xff0c;使用WITH (NOLOCK)就要好好考虑一下。

如果不需考虑transaction&＃xff0c;WITH (NOLOCK)或许是个好用的参考。

Sqlserver高效分页

在SQLserver2012之前&＃xff0c;分页我们一般是使用ROW_NUMBER()。以获取第11条到第50条数据为例&＃xff1a;

ROW_NUMBER()的实现方式&＃xff1a;

select * from(select *, ROW_NUMBER() OVER(Order by a.OrderID DESC ) AS RowNumber from Orders asa

)asbwhere RowNumber BETWEEN 11 and 50

SQLserver2012中OFFSET & FETCH实现方式&＃xff1a;

SELECT o.* FROMdbo.Orders oORDER 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 行计数表达式可以是将返回整数值的任何算术、常量或参数表达式。该行计数表达式不支持标量子查询

ALTER PROCEDURE getLightningSendOverTimeOrder --闪电送到期存储过程

(&＃64;OverTimeMinutes INT,--超时时间阈值

&＃64;GetLasterDay int --取最近多少天的订单

)As

DECLARE &＃64;sql NVARCHAR(MAX);BEGIN

set nocount on;set &＃64;Sql&＃61;N&＃39;WITH OrderInfo

--查询近一天的&＃xff0c;处理超时的闪电送订单

AS ( SELECT ID ,

CommunityId ,

SubOrderNumber

FROM dbo.[Order] WITH ( NOLOCK )

WHERE DATEDIFF(day, CreatedOn, GETDATE()) <&＃61; &＃64;GetLasterDay

AND DATEDIFF(mi, CreatedOn, ModifiedOn) > &＃64;OverTimeMinutes

AND LightningSend &＃61; 1

AND IsDeleted &＃61; 0

--待付款(商品订单),待确认(商品订单),待发货(商品订单),待收货(商品订单)

AND OrderStatusId IN (&＃39;&＃39;C2EE784F-F29B-4E18-8D73-761264339005&＃39;&＃39;,&＃39;&＃39;FA1A31DF-3855-41BB-9F5C-A09F3AB4C408&＃39;&＃39;,&＃39;&＃39;ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5&＃39;&＃39;,&＃39;&＃39;15F06407-C82C-4CA8-984E-37FB3BD9963C&＃39;&＃39;)

),

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 &＃61; 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 &＃61; c.ID

INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID &＃61; oi.OrderId

INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId &＃61; p.Id

AND p.LightningSend &＃61; 1

AND p.ProcessType !&＃61; 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 &＃61; c.ID

INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID &＃61; oi.OrderId

INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId &＃61; p.Id

AND p.LightningSend &＃61; 1

AND p.ProcessType &＃61; 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 &＃61; usr.OrgId

INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId &＃61; org.Id

AND org.Levels &＃61; 2 --便利店

INNER JOIN OrderDistributaryCategoryRelation AS odcr

WITH ( NOLOCK ) ON usr.Id &＃61; odcr.MerchantUserId

AND odcr.CategoryType &＃61; 1

AND odcr.SellerId &＃61; soi.ConvenienceStoreID

AND odcr.CategoryId &＃61; soi.CategoryId

INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id &＃61; odb.MerchantUserId

AND odb.SellerId &＃61; soi.ConvenienceStoreID

INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON usr.Id &＃61; odbr.MerchantUserId

AND odbr.MerchantId &＃61; 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 &＃61; odb.MerchantUserId

AND odb.IsDefault &＃61; 1

INNER JOIN SmsOrderInfo soi WITH ( NOLOCK ) ON odb.SellerId &＃61; 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 &＃61; usr.OrgId

INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId &＃61; org.Id

AND org.Levels &＃61; 1 --商家

INNER JOIN OrderDistributaryCategoryRelation AS odcr

WITH ( NOLOCK ) ON usr.Id &＃61; odcr.MerchantUserId

AND odcr.CategoryType &＃61; 1

AND odcr.CategoryId &＃61; soi.CategoryId

INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id &＃61; odb.MerchantUserId

AND odb.SellerId &＃61; soi.PublisherId

INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON odcr.MerchantUserId &＃61; odbr.MerchantUserId

AND odbr.MerchantId &＃61; 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 &＃61; soi.PublisherId

AND odb.IsDefault &＃61; 1

AND odb.IsDeleted &＃61; 0

INNER JOIN dbo.[User] u WITH ( NOLOCK ) ON u.Id &＃61; odb.MerchantUserId

)

SELECT r.SubOrderNumber ,

r.UserId,

r.PhoneNumber

FROM ResultInfo r&＃39;

exec sp_executesql &＃64;Sql,N&＃39;&＃64;OverTimeMinutes int,&＃64;GetLasterDay int&＃39;,&＃64;OverTimeMinutes,&＃64;GetLasterDay;SET NOCOUNT OFF;END

View Code

自己写的一个存储过程例子

--------------------------------------创建闪电送存储过程-----------------------------------

IF EXISTS(Select Name From Sysobjects Where Name&＃61;&＃39;getLightningSendOverTimeOrder&＃39; And Xtype&＃61;&＃39;P&＃39;)DROP PROCEDURE [dbo].getLightningSendOverTimeOrderGO

CREATE PROCEDURE getLightningSendOverTimeOrder --闪电送到期存储过程

(&＃64;OverTimeMinutes INT,--超时时间阈值

&＃64;GetLasterDay int --取最近多少天的订单

)As

DECLARE &＃64;sql NVARCHAR(MAX);BEGIN

set nocount on;set &＃64;Sql&＃61;N&＃39;WITH OrderInfo

--查询近一天的&＃xff0c;处理超时的闪电送订单

AS ( SELECT ID ,

CommunityId ,

SubOrderNumber

FROM dbo.[Order] WITH ( NOLOCK )

WHERE DATEDIFF(day, CreatedOn, GETDATE()) <&＃61; &＃64;GetLasterDay

AND DATEDIFF(mi, CreatedOn, ModifiedOn) > &＃64;OverTimeMinutes

AND LightningSend &＃61; 1

AND IsDeleted &＃61; 0

--待付款(商品订单),待确认(商品订单),待发货(商品订单),待收货(商品订单)

AND OrderStatusId IN (&＃39;&＃39;C2EE784F-F29B-4E18-8D73-761264339005&＃39;&＃39;,&＃39;&＃39;FA1A31DF-3855-41BB-9F5C-A09F3AB4C408&＃39;&＃39;,&＃39;&＃39;ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5&＃39;&＃39;,&＃39;&＃39;15F06407-C82C-4CA8-984E-37FB3BD9963C&＃39;&＃39;)

UNION ALL

--状态一直是待支付状态的

SELECT ID ,

CommunityId ,

SubOrderNumber

FROM dbo.[Order] WITH ( NOLOCK )

WHERE DATEDIFF(day, CreatedOn, GETDATE()) <&＃61;&＃64;GetLasterDay

AND LightningSend &＃61; 1

AND IsDeleted &＃61; 0

AND (OrderStatusId &＃61;&＃39;&＃39;ACF190B2-0CA2-43A0-8E6B-70DF8521F4C5&＃39;&＃39;AND DATEDIFF(mi, CreatedOn, GETDATE()) >&＃64;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 &＃61; 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 &＃61; c.ID

INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID &＃61; oi.OrderId

INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId &＃61; p.Id

AND p.LightningSend &＃61; 1

AND p.ProcessType !&＃61; 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 &＃61; c.ID

INNER JOIN dbo.OrderItem oi WITH ( NOLOCK ) ON so.ID &＃61; oi.OrderId

INNER JOIN dbo.Product p WITH ( NOLOCK ) ON oi.ProductId &＃61; p.Id

AND p.LightningSend &＃61; 1

AND p.ProcessType &＃61; 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 &＃61; usr.OrgId

INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId &＃61; org.Id

AND org.Levels &＃61; 2 --便利店

INNER JOIN OrderDistributaryCategoryRelation AS odcr

WITH ( NOLOCK ) ON usr.Id &＃61; odcr.MerchantUserId

AND odcr.CategoryType &＃61; 1

AND odcr.SellerId &＃61; soi.ConvenienceStoreID

AND odcr.CategoryId &＃61; soi.CategoryId

INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id &＃61; odb.MerchantUserId

AND odb.SellerId &＃61; soi.ConvenienceStoreID

INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON usr.Id &＃61; odbr.MerchantUserId

AND odbr.MerchantId &＃61; 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 &＃61; odb.MerchantUserId

AND odb.IsDefault &＃61; 1

INNER JOIN SmsOrderInfo soi WITH ( NOLOCK ) ON odb.SellerId &＃61; 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 &＃61; usr.OrgId

INNER JOIN dbo.Organization org WITH ( NOLOCK ) ON usr.OrgId &＃61; org.Id

AND org.Levels &＃61; 1 --商家

INNER JOIN OrderDistributaryCategoryRelation AS odcr

WITH ( NOLOCK ) ON usr.Id &＃61; odcr.MerchantUserId

AND odcr.CategoryType &＃61; 1

AND odcr.CategoryId &＃61; soi.CategoryId

INNER JOIN OrderDistributary AS odb WITH ( NOLOCK ) ON usr.Id &＃61; odb.MerchantUserId

AND odb.SellerId &＃61; soi.PublisherId

INNER JOIN OrderDistributaryRelation AS odbr WITH ( NOLOCK ) ON odcr.MerchantUserId &＃61; odbr.MerchantUserId

AND odbr.MerchantId &＃61; 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 &＃61; soi.PublisherId

AND odb.IsDefault &＃61; 1

AND odb.IsDeleted &＃61; 0

INNER JOIN dbo.[User] u WITH ( NOLOCK ) ON u.Id &＃61; odb.MerchantUserId

)

SELECT r.SubOrderNumber ,

r.UserId,

r.PhoneNumber

FROM ResultInfo r&＃39;

exec sp_executesql &＃64;Sql,N&＃39;&＃64;OverTimeMinutes int,&＃64;GetLasterDay int&＃39;,&＃64;OverTimeMinutes,&＃64;GetLasterDay;SET NOCOUNT OFF;END

GO

View Code

demo2:分页存储过程

------------------------------------------创建我的优惠券存储过程 created by zouqj-2017-3-1-----------------------------------

IF EXISTS(Select Name From Sysobjects Where Name&＃61;&＃39;usp_GetMyCouponsList&＃39; And Xtype&＃61;&＃39;P&＃39;)DROP PROCEDUREusp_GetMyCouponsListGO

CREATE PROCusp_GetMyCouponsList

(&＃64;PageIndex int, --页码

&＃64;PageSize int, --&＃64;hostName nvarchar(100),--服务器地址

&＃64;MemberID UNIQUEIDENTIFIER, --会员ID

&＃64;IsGetTotalInfo BIT, --是否得到统计信息&＃xff0c;1为得到统计信息&＃xff0c;0为不得到统计信息&＃xff0c;返回记录集

&＃64;HasStatus BIT, --是否有状态

&＃64;CouponsStatus INT --优惠券状态

)AS

----------------------------------------定义参数--------------------------------------

declare &＃64;strSql NVARCHAR(max);DECLARE &＃64;IsUseCount INT

DECLARE &＃64;ExpiredCount INT

DECLARE &＃64;NoUseCount INT

DECLARE &＃64;where NVARCHAR(100);set nocount on;----------------------------------------SQL开始--------------------------------------

IF &＃64;HasStatus&＃61;1

BEGIN

SET &＃64;where&＃61;&＃39;m.[State]&＃61;&＃64;CouponsStatus&＃39;;END

ELSE

BEGIN

SET &＃64;where&＃61;&＃39;1&＃61;1&＃39;

END

IF &＃64;IsGetTotalInfo&＃61;0

BEGIN

SET &＃64;strSql&＃61;N&＃39;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 &＃61;1 THEN 1 ELSE 2 END ) as IsLimited,

c.FullAmount as MinAmount,

e.[Type] AS CouponsType,

(CASE WHEN b.IconUrl IS NULL THEN N&＃39;&＃39;&＃39;&＃39;WHEN b.IconUrl&＃61;N&＃39;&＃39;&＃39;&＃39;THEN N&＃39;&＃39;&＃39;&＃39;ELSE &＃64;hostName&＃43;b.IconUrl END) AS IconUrl

FROM Loy_MemberCoupon m WITH ( NOLOCK ) INNER JOIN

[dbo].[MK_Campaign] AS a WITH ( NOLOCK ) ON m.CampaignID&＃61;a.ID AND m.MemberID&＃61;&＃64;MemberID AND&＃39;&＃43;&＃64;where&＃43;&＃39;LEFT OUTER JOIN [dbo].[MK_CouponConfig] AS b WITH ( NOLOCK ) ON a.[ID] &＃61; b.[CampaignID]

LEFT OUTER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] &＃61; c.[ID]

LEFT OUTER JOIN [dbo].[MK_CouponRestriction] AS d WITH ( NOLOCK ) ON c.[ID] &＃61; d.[CompainID]

LEFT OUTER JOIN [dbo].[MK_CouponRestrictCategory] AS e WITH ( NOLOCK ) ON d.[ID] &＃61; e.[CouponRestrictionID]

) t

ORDER BY t.CampaignEndTime

offset (&＃64;PageIndex-1)*&＃64;PageSize ROWS FETCH NEXT &＃64;PageIndex*&＃64;PageSize ROWS ONLY&＃39;

exec sp_executesql &＃64;strSql,N&＃39;&＃64;PageIndex int,&＃64;PageSize int,&＃64;hostName nvarchar(100),&＃64;MemberID UNIQUEIDENTIFIER,&＃64;where NVARCHAR(100),&＃64;CouponsStatus int&＃39;,&＃64;PageIndex&＃61;&＃64;PageIndex,&＃64;PageSize&＃61;&＃64;PageSize,&＃64;hostName&＃61;&＃64;hostName,&＃64;MemberID&＃61;&＃64;MemberID,&＃64;where&＃61;&＃64;where,&＃64;CouponsStatus&＃61;&＃64;CouponsStatus

END

ELSE

BEGIN

-------------------------------------------------------------

SELECT &＃64;IsUseCount&＃61;COUNT(1) FROM (SELECT DISTINCTm.CouponCodeFROM Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID&＃61;&＃64;MemberID AND m.[State]&＃61;2) tSELECT &＃64;ExpiredCount&＃61;COUNT(1) FROM (SELECT DISTINCTm.CouponCodeFROM Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID&＃61;&＃64;MemberID AND m.[State]&＃61;3) tSELECT &＃64;NoUseCount&＃61;COUNT(1) FROM (SELECT DISTINCTm.CouponCodeFROM Loy_MemberCoupon m WITH ( NOLOCK ) WHERE m.MemberID&＃61;&＃64;MemberID AND m.[State]&＃61;1) tSELECT &＃64;IsUseCount AS IsUseCount,&＃64;ExpiredCount AS ExpiredCount,&＃64;NoUseCount ASNoUseCountEND

----------------------------------------SQL结束--------------------------------------

set nocount off;

View Code