在 SQL Server 中声明的查询变量中创建新列 [英] Create new columns in a declared query variable in SQL Server

问题描述

我在 SQL Server 2008 R2 中创建了一个存储过程.它执行一个数据透视表.所以，接下来的步骤我需要的是 3 个新列，它们将在这个声明的查询变量中创建.在这 3 列中，我必须执行计算.

I've created a stored procedure in SQL Server 2008 R2. It executes a pivot table. So, what I need for the next steps are 3 new columns, which would be create in this declared query variable. In these 3 columns I have to perform a calculation.

首先，我的存储过程代码:更新存储过程:

At first, my stored procedures code: UPDATE Stored Procedure:

BEGIN
    SET NOCOUNT ON;

    DECLARE @colNo nvarchar(max)
    DECLARE @colSum nvarchar(max)
    DECLARE @query nvarchar(max)

    SET NOCOUNT ON;

    WITH vals AS (
        SELECT DISTINCT ds.No
        FROM QRTestView ds
    )

    SELECT @colNo = COALESCE(@colNo + ', ', '') + QUOTENAME(No)
           @colSum = COALESCE(@colSum + '+ ', '') + QUOTENAME(No)
    FROM vals
    ORDER BY No

    SET @query = 'SELECT *, [Bonus] + 125 as [Fee], 
                   ([Bonus] + 125) * [ALL] as [Fee2]
                    FROM (
                      SELECT *,
                        CASE WHEN 70-(100 - Quote) * 10 > -80 THEN 70-(100 - Quote) * 10  ELSE -70 END as [Bonus]
                      FROM (
                        SELECT *, '+@colSum+' as [ALL]
                        FROM (
                          SELECT 
                             CAST(ISIN as CHAR(12)) As No,
                             CAST(Quote as CHAR(7)) As Quote,
                             CAST(Quote as CHAR(7)) As Q
                          FROM QRTestView
                          WHERE Datum >= @from_val and Datum <= @to_val
                        ) AS sel
                        PIVOT (
                          COUNT(Q)
                          FOR No IN ('+ @colNo +')
                        ) AS p

                      UNION ALL

                      SELECT ''ALL'', *, '+@colSum+'[ALL]
                      FROM (
                        SELECT
                           CAST(No as CHAR(12)) As No,
                           COUNT(CAST(Quote as CHAR(7))) As Quote
                        FROM QRTestView
                        WHERE Datum >= @from_val and Datum <= @to_val
                        GROUP BY CAST(No as CHAR(12))
                      ) AS sel
                      PIVOT (
                        MAX(Quote) 
                        FOR No in ('+ @colNo +')
                      ) AS p
                     ) AS sel2
                  ) as ff

            ORDER BY CASE WHEN Quote = ''ALL'' THEN 101 ELSE CAST(Quote as INT) END DESC'

    EXEC sp_executesql @query, N'@from_val datetime, @to_val datetime', @from_val = @from, @to_val = @to
END

为了理解问题，我给你看源表:

To understand the question, I will show you the source table:

| No      | Quote | Datum
| DE10101 | 100   | 2016-01-01
| DE10121 | 100   | 2016-01-02
| DE10101 | 100   | 2016-01-05
| DE22034 | 98    | 2016-01-05
| DE10101 | 98    | 2016-01-10
| DE10121 | 80    | 2016-01-10
| DE22034 | 98    | 2016-01-10
| DE22034 | 80    | 2016-01-11
| DE10101 | 100   | 2016-01-20
| DE10121 | 80    | 2016-01-21

目标表还有 3 个额外的列:

And the target table has 3 additional columns:

| Quote | DE10101 | DE10121 | DE22034 | ALL | Bonus | Fee | Fee2 |
| 100   |    3    |    1    |    0    |  4  |  70   | 195 | 780  |
|  98   |    1    |    0    |    2    |  3  |  50   | 175 | 525  |
|  80   |    0    |    2    |    1    |  3  | -70   |  55 | 165  |
| ALL   |    4    |    3    |    3    | 10  |       |     | 1470 |

新增的 3 列是:Bonus、Fee、Fee2

计算方式如下:

奖励:WHEN 70-(100 - Quote) * 10 >-80然后 70-(100 - 报价) * 10其他 -70

费用:奖金 + 125

费用2:所有*费用

如何在存储过程的查询变量中定义这 3 列和计算?

How can I define these 3 columns and calculation in my query variable in the stored procedure?

推荐答案

需要使用CTE

SET NOCOUNT ON;

DECLARE @colNo nvarchar(max)
DECLARE @SUMCols nvarchar(max)
DECLARE @query nvarchar(max)


SET NOCOUNT ON;

WITH vals AS (
    SELECT DISTINCT ds.[No]
    FROM QRTestView ds
)

SELECT @colNo = COALESCE(@colNo + ', ', '') + QUOTENAME([No]),
       @SUMCols = COALESCE(@SUMCols + ', ', '') + 'SUM(' + QUOTENAME([No]) +')'
FROM vals
ORDER BY No


SELECT @query = N'
;WITH cte as (
SELECT  *, 
        [Bonus] + 125 as [Fee], 
        ([Bonus] + 125) * [ALL] as [Fee2]
FROM (
    SELECT  *,
            CASE WHEN 70-(100 - Quote) * 10 > -80 THEN 70-(100 - Quote) * 10  ELSE -70 END as [Bonus]
    FROM (
        SELECT  *, 
                '+REPLACE(@colNo,',','+')+'  as [ALL]
        FROM (
            SELECT  CAST(No as CHAR(12)) As No,
                    CAST(Quote as CHAR(7)) As Quote,
                    CAST(Quote as CHAR(7)) As Q
            FROM QRTestView
            WHERE Datum >= @from_val and Datum <= @to_val
            ) AS sel
            PIVOT (
                COUNT(Q)
                FOR No IN ('+@colNo+')
            ) AS p
        ) AS d
    ) as ff
)

SELECT *
FROM (
    SELECT  *
    FROM cte
    UNION ALL
    SELECT  ''ALL'', 
            '+@SUMCols+'
            SUM([ALL]),
            NULL,
            NULL,
            SUM(Fee2)
    FROM cte
    ) as t
ORDER BY CASE WHEN Quote = ''ALL'' THEN 101 ELSE CAST(Quote as INT) END DESC'

EXEC sp_executesql @query, N'@from_val datetime, @to_val datetime', @from_val = @from, @to_val = @to

这篇关于在 SQL Server 中声明的查询变量中创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！