由新近修改的一个报表想到的

由最近修改的一个报表想到的

最近接到新的需求，要求对一个很古老的报表(酒店销售报表hotel sales report)进行修改和优化，原来这个报表包含两种订单来源的订单，HBS和PBS，现在的需求是，一是要分别限制HBS和PBS的订单范围，二是要增加一列，显示订单的取消原因，取消原因分风控过程取消和非风控过程取消。

第一个需求，分别限制HBS和PBS的订单范围，但是这个限定的逻辑和标准还不太一样，所以我的想法是，创建一个自定义函数function，输入订单号OrderID，具体的逻辑封装在这个函数内，然后这个函数返回一个布尔值就可以。

对于第二个需求，加一列显示订单取消原因，我的想法是，先把风控过程取消的订单和取消原因拿出来做为第一个集合，然后再把非风控过程取消的订单和取消原因取出来作为第二个集合，再将这两个集合UNION ALL，得到所有取消订单及取消原因的集合，在查询的时候LEFT JOIN这个集合就可以。

这个报表所关联的订单表数据量非常大，所以查询时的性能问题也必须得考虑。

函数的使用

当我们要封装一段业务逻辑，并返回一个结果的时候，可以选择使用函数。比如我这里的第一个需求，封装了HBS和PBS界定订单范围的逻辑，返回布尔值结果，是否属于这个范围就可以。

函数也分为标量函数和表值函数，标量函数返回一个值，而表值函数返回多个值(或者说一个表)。我这里使用的就是标量函数。如下代码。

USE HotelDB;
GO

--UDF，判断订单是否成功提交永安
--如果是HBS订单，依据OrderID,关联OrderLog表，查询是否有【提交成功】的邮件，如果存在则为true，否则为false
--如果是PBS携程订单，只要成功创建永安(本地)订单的都作为‘成功提交永安的订单’。
IF OBJECT_ID('dbo.fn_GetIsSuccessSubmitWingon') IS NOT NULL
    DROP FUNCTION dbo.fn_GetIsSuccessSubmitWingon;
GO


CREATE FUNCTION dbo.fn_GetIsSuccessSubmitWingon
    (
      @OrderID AS INT ,
      @CategoryType AS VARCHAR(50)
    )
RETURNS INT
AS
    BEGIN
        DECLARE @Result AS INT;
        DECLARE @IsPBSOrder AS BIT;
        DECLARE @PkgOrderNo AS VARCHAR(10);
        
        IF ( @CategoryType NOT IN ( 'HBS', 'PBS' ) )
            
            --是否PBS订单
            IF EXISTS ( SELECT  *
                        FROM    dbo.HotelOrder (NOLOCK) AS hotelOrder
                        WHERE   hotelOrder.OrderID = @OrderID
                                AND hotelOrder.OrderType NOT IN ( 1, 2, 3, 4,
                                                              5, 11, 12, 13 )
                                AND hotelOrder.ResourceFromFlagType = 2 )
                SET @IsPBSOrder = 1;
            ELSE
                SET @IsPBSOrder = 0;
        ELSE
            IF ( @CategoryType = 'PBS' )
                SET @IsPBSOrder = 1;
            ELSE
                SET @IsPBSOrder = 0; 
        
        IF ( @IsPBSOrder = 1 )--PBS订单
            BEGIN
                SET @Result = 1;            
                --SELECT  @PkgOrderNo = orderMapping.MappingID
                --FROM    dbo.OrderMapping (NOLOCK) AS orderMapping
                --WHERE   orderMapping.HotelOrderID = @OrderID;
                
                --IF EXISTS ( SELECT  *
                --            FROM    HP580DBSZ.PackageFHDB.dbo.Pkg_OrderLog AS orderLog
                --            WHERE   orderLog.PkgOrderNo = @PkgOrderNo
                --                    AND orderLog.OperationType = 1--成功提交度假订单
                --                    AND orderLog.Operator = 'System' )
                --    SET @Result = 1;
                --ELSE
                --    SET @Result = 0;                
            
            END;
        ELSE
            BEGIN
                IF EXISTS ( SELECT  *
                            FROM    dbo.HotelOrderLog (NOLOCK) AS orderLog
                            WHERE   orderLog.OrderID = @OrderID
                                    AND orderLog.OperationType = 4--发邮件
                                    AND orderLog.NewOrderStatus = 10
                                    AND orderLog.Remark LIKE N'【提交成功】%' )
                    SET @Result = 1;
                ELSE
                    SET @Result = 0;
            END;
        
        RETURN @Result;
    END;

标量函数一般用在SELECT和WHERE语句中。

使用在SELECT语句中，既可以作为一列，也可以作为条件(一般在CASE WHEN语句中)。

使用在WHERE语句中，只用为条件来使用，如下代码。

AND dbo.fn_GetIsSuccessSubmitWingon(a.OrderID, @CategoryType) > 0--已成功提交永安

到底使用临时表还是表表达式

在实现第二个需求的时候，一开始我使用的是表表达式，因为方便简洁，但是后面发现不行，因为表表达式的生命周期随着第一个外部查询完成就结束了，比如下面这样就不行。

USE HotelDB;

GO

--CTE

WITH NoCardRiskCancelOrders AS

(

 SELECT  hotelOrder.OrderID ,

            hotelOrder.CreateTime ,

            hotelOrder.CancelReason

    --INTO    #NoCardRiskCancelOrders

    FROM    dbo.HotelOrder (NOLOCK) AS hotelOrder

            LEFT JOIN dbo.HotelOrderExtend AS orderExtend ON orderExtend.OrderID = hotelOrder.OrderID

    WHERE   hotelOrder.OrderStatus = 40

            AND orderExtend.CardRiskStatus IS NULL

)

--第一个外部查询

SELECT TOP 10 * FROM dbo.HotelOrder (NOLOCK) AS hotelOrder;

--使用CTE ，报错

SELECT * FROM NoCardRiskCancelOrders;

因为这个结果集在后面要多次被用到，所以最后我选择了传统的临时表来实现。如下代码。

--2，非风控过程取消订单

    SELECT  hotelOrder.OrderID ,

            hotelOrder.CreateTime ,

            hotelOrder.CancelReason

    INTO    #NoCardRiskCancelOrders

    FROM    dbo.HotelOrder (NOLOCK) AS hotelOrder

            LEFT JOIN dbo.HotelOrderExtend AS orderExtend ON orderExtend.OrderID = hotelOrder.OrderID

    WHERE   hotelOrder.OrderStatus = 40

            AND orderExtend.CardRiskStatus IS NULL

            AND ( @BegBookingDate IS NULL

                  OR hotelOrder.CreateTime >= @BegBookingDate

                )

            AND ( @EndBookingDate IS NULL

                  OR hotelOrder.CreateTime < DATEADD(ms, -4,

                                                     DATEADD(d, 1,

                                                             @EndBookingDate))

                );

--暂时将结果集放到临时表中

    SELECT  *

    INTO    dbo.#t1

    FROM    dbo.#CardRiskCancelOrders

    UNION ALL

    SELECT  *

    FROM    dbo.#NoCardRiskCancelOrders;

输出的字段有多种情形时使用CASE...WHEN

我们的报表有些字段展示很多时候有多种情形，每个情形显示的结果不一样，一般我们可以在SELECT中使用CASE...WHEN来实现。

比如，这个报表中字段NoShowStatus的展示逻辑是这样的。

CASE WHEN BalanceType = 'PP' THEN ''

                 WHEN OrderStatusFlag & 2048 = 2048 THEN N'否'

                 WHEN OrderStatusFlag & 16384 = 16384 THEN N'是'

                 WHEN OrderStatusFlag & 8192 = 8192 THEN N'複核中'

                 WHEN OrderStatusFlag & 4096 = 4096 THEN N'未複核'

                 ELSE N'否'

            END NoShowStatus ,

你真的会用联接查询吗

我们一般使用得比较多的是内联接查询(INNER JOIN)和外联接查询(LEFT JOIN)这两种。

内联接查询有两个处理步骤：笛卡尔积运算和过滤。

外联接查询有三个处理步骤：笛卡尔积运算，过滤和添加外部行。

要理解外部行，首先要明白什么是保留行，保留行指的是LEFT关键字左边的那些行，添加外部行，就是保留行中按照ON条件在另一个表找不到与之匹配的那些行，然后把这些行添加到LEFT左边生成的结果表中，需要注意的是，对于来自联接的非保留表的那些列，添加的外部行中的这些列的值为NULL。

对于外联接，如果只是做联接，是不会影响最终输出的记录数的，只有使用在WHERE语句中才会影响最终输出的记录数的。

性能很重要

对于报表来说，性能很重要。

通常我们可以通过以下几个手段来提高性能。

1，缩小查询范围。缩小查询范围相当于减少数据量。比如有查询条件预订日期的，我们可以带上这个条件。

2，合理使用联接查询。有些使用内联接和外联接都可以的情况下，尽量使用内联接，因为外联接会添加外部行，增加数据量。

3，相关字段建立索引，这是比较通常的做法。