c# - SQL 查询正在复制行并在我的聚合函数中使用这些行-6ren

c# - SQL 查询正在复制行并在我的聚合函数中使用这些行

转载作者：太空宇宙更新时间：2023-11-03 12:32:07

您好，抱歉，这可能会很长，我突然遇到了一个奇怪的问题。我正在尝试创建一个类似会计的应用程序，其中多个列由它们所属的组聚合。但是，其中一些列可能包含我不想包含在总计中的重复数据。

我的 table 看起来像:

CREATE TABLE [dbo].[RawDataTable] (
[Id]                     INT             IDENTITY (1, 1) NOT NULL,
[CheckDate]              DATE            NULL,
[PropNum]                INT             NOT NULL,
[PropSeqNum]             INT             NULL,
[PropName]               NVARCHAR (100)  NOT NULL,
[ProductionMonth]        INT             NULL,
[Product]                INT             NOT NULL,
[LeaseVolume]            DECIMAL (18, 2) NOT NULL,
[Price]                  DECIMAL (18, 2) NOT NULL,
[LeaseGrossValue]        DECIMAL (18, 2) NOT NULL,
[LeaseTaxes]             DECIMAL (18, 2) NULL,
[LeaseOtherDeductions]   DECIMAL (18, 2) NOT NULL,
[LeaseNetValue]          DECIMAL (18, 2) NOT NULL,
[DisbursementDecimal]    DECIMAL (18)    NULL,
[InterestType]           NVARCHAR (10)   NULL,
[InterestGrossValue]     DECIMAL (18, 2) NOT NULL,
[InterestTaxes]          DECIMAL (18, 2) NOT NULL,
[IntrestOtherDeductions] DECIMAL (18, 2) NOT NULL,
[InterestNetValue]       DECIMAL (18, 2) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

首先，我尝试使用此存储过程去除任何重复数据:

CREATE PROCEDURE [dbo].[EraseDuplicates]
AS
UPDATE  T1
SET     T1.LeaseVolume = 0,
        T1.Price = 0,
        T1.LeaseGrossValue = 0,
        T1.LeaseTaxes = 0,
        T1.LeaseOtherDeductions = 0,
        T1.LeaseNetValue = 0
FROM
    (SELECT  *
     FROM    RawDataTable
     WHERE   Product >= 400 OR
             Id NOT IN 
                (SELECT MIN(Id)
                 FROM RawDataTable
                 GROUP BY CheckDate, PropNum, PropSeqNum, PropName, ProductionMonth, Product)) AS T1

当我从 EraseDuplicates 存储过程返回时，我的问题来了，它正确地将数据清零，但是它也复制行并将它们添加回表中，结果如下:

主要编辑

我已经将范围缩小到这个存储过程。一旦我从这个方法返回

public static void EraseDuplicateData(string connString)
    {
        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString))
        {
            db.Query("EraseDuplicates", commandType: CommandType.StoredProcedure);
        }
    }

数据库突然重复了这些行。这些值已正确归零，但我只是不明白更新命令如何创建行。

原始数据

+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
|  CheckDate |  PropNum |  PropSeqNum |            PropName           |  ProductionMonth |  Product |  LeaseVolume |  Price |  LeaseGrossValue |  LeaseTaxes |  LeaseOtherDeductions |  LeaseNetValue |  DisbursementDecimal |  InterestType |  InterestGrossValue |  InterestTaxes |  IntrestOtherDeductions |  InterestNetValue |  RecordCount |  CheckAmount |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       | 0.0026932            | RI 01         | 108.38              | -12.46         | 0                       | 95.92             |              |              |  |  |  |
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       | 0.0050669            | RI 02         | 203.91              | -23.45         | 0                       | 180.46            |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      204 | -0.27        | 4.037  | -1.09            | -0.11       | 0.13                  | -1.07          | 0.0026932            | RI 01         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 116.76       | 3.992  | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0026932            | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 116.76       | 3.992  | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0050669            | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      400 | -1.59        | 1.1006 | -1.75            | 0           | 0                     | -1.75          | 0.0050669            | RI 02         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 3380.17      | 0.6214 | 2100.4           | 0           | 0                     | 2100.4         | 0.0026932            | RI 01         | 5.66                | 0              | 0                       | 5.66              |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 3380.17      | 0.6214 | 2100.4           | 0           | 0                     | 2100.4         | 0.0050669            | RI 02         | 10.64               | 0              | 0                       | 10.64             |              |              |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+

预期结果

+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
|  CheckDate |  PropNum |  PropSeqNum |            PropName           |  ProductionMonth |  Product |  LeaseVolume |  Price |  LeaseGrossValue |  LeaseTaxes |  LeaseOtherDeductions |  LeaseNetValue |  DisbursementDecimal |  InterestType |  InterestGrossValue |  InterestTaxes |  IntrestOtherDeductions |  InterestNetValue |  RecordCount |  CheckAmount |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       | 0.0026932            | RI 01         | 108.38              | -12.46         | 0                       | 95.92             |              |              |  |  |  |
| 1/25/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0025 |           122014 |      100 | 0            | 0      | 0                | 0           | 0                     | 0              | 0.0050669            | RI 02         | 203.91              | -23.45         | 0                       | 180.46            |              |              |  |  |  |
| 1/31/2015  |   100004 |          25 | BEAVER LODGE DEVON UT TR-0026 |                  |      100 | 774.96       | 51.93  | 40243.64         | -4628.03    | 0                     | 35615.61       |                      |               | 312.29              | -35.91         | 0                       | 276.38            |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      204 | -0.27        | 4.037  | -1.09            | -0.11       | 0.13                  | -1.07          | 0.0026932            | RI 01         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 116.76       | 3.992  | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0026932            | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      204 | 0            | 0      | 0                | 0           | 0                     | 0              | 0.0050669            | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 1/31/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |                  |      204 | 116.49       | 8.029  | 465.02           | -60.74      | -1511.41              | -1107.13       |                      |               | 3.61                | -0.47          | -11.73                  | -8.59             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |            42013 |      400 | -1.59        | 1.1006 | -1.75            | 0           | 0                     | -1.75          | 0.0050669            | RI 02         | -0.01               | 0              | 0                       | -0.01             |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 3380.17      | 0.6214 | 2100.4           | 0           | 0                     | 2100.4         | 0.0026932            | RI 01         | 5.66                | 0              | 0                       | 5.66              |              |              |  |  |  |
| 1/25/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |           112014 |      400 | 0            | 0      | 0                | 0           | 0                     | 0              | 0.0050669            | RI 02         | 10.64               | 0              | 0                       | 10.64             |              |              |  |  |  |
| 1/31/2015  |   100004 |       40111 | BEAVER LODGE DEVON UT TR-0025 |                  |      400 | 3378.58      | 1.722  | 2098.65          | 0           | 0                     | 2098.65        |                      |               | 16.29               | 0              | 0                       | 16.29             |              |              |  |  |  |
+------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+

实际结果(删除重复程序后)

+-----+-----------------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| Id  |       CheckDate       |  PropNum |  PropSeqNum |            PropName           |  ProductionMonth |  Product |  LeaseVolume |  Price |  LeaseGrossValue |  LeaseTaxes |  LeaseOtherDeductions |  LeaseNetValue |  DisbursementDecimal |  InterestType |  InterestGrossValue |  InterestTaxes |  IntrestOtherDeductions |  InterestNetValue |  RecordCount |  CheckAmount |  |  |  |
+-----+-----------------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+
| 464 | 1/1/0001 12:00:00 AM  | NULL     | NULL        |                               | NULL             | NULL     | NULL         | NULL   | NULL             | NULL        | NULL                  | NULL           | NULL                 |               | NULL                | NULL           | NULL                    | NULL              |              |              |  |  |  |
| 926 | 1/1/0001 12:00:00 AM  | NULL     | NULL        |                               | NULL             | NULL     | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | NULL                 |               | NULL                | NULL           | NULL                    | NULL              |              |              |  |  |  |
| 465 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 203.91              | -23.45         | 0.00                    | 180.46            |              |              |  |  |  |
|   1 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 774.96       | 51.93  | 40243.64         | -4628.03    | 0.00                  | 35615.61       | 0.0026932000         | RI 01         | 108.38              | -12.46         | 0.00                    | 95.92             |              |              |  |  |  |
|   2 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 203.91              | -23.45         | 0.00                    | 180.46            |              |              |  |  |  |
|  29 | 1/25/2015 12:00:00 AM | 100004   | 25          | BEAVER LODGE DEVON UT TR-0025 | 122014           | 100      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 108.38              | -12.46         | 0.00                    | 95.92             |              |              |  |  |  |
|  44 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 204      | -0.27        | 4.03   | -1.09            | -0.11       | 0.13                  | -1.07          | 0.0026932000         | RI 01         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
|  46 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 116.76       | 3.99   | 466.11           | -60.63      | -1511.54              | -1106.06       | 0.0026932000         | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
|  47 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 506 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
| 508 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 1.26                | -0.16          | -4.07                   | -2.97             |              |              |  |  |  |
| 509 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 204      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 2.36                | -0.31          | -7.66                   | -5.61             |              |              |  |  |  |
| 510 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 5.66                | 0.00           | 0.00                    | 5.66              |              |              |  |  |  |
| 511 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 10.64               | 0.00           | 0.00                    | 10.64             |              |              |  |  |  |
| 507 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
|  48 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0026932000         | RI 01         | 5.66                | 0.00           | 0.00                    | 5.66              |              |              |  |  |  |
|  49 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 112014           | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | 10.64               | 0.00           | 0.00                    | 10.64             |              |              |  |  |  |
|  45 | 1/25/2015 12:00:00 AM | 100004   | 40111       | BEAVER LODGE DEVON UT TR-0025 | 42013            | 400      | 0.00         | 0.00   | 0.00             | 0.00        | 0.00                  | 0.00           | 0.0050669000         | RI 02         | -0.01               | 0.00           | 0.00                    | -0.01             |              |              |  |  |  |
+-----+-----------------------+----------+-------------+-------------------------------+------------------+----------+--------------+--------+------------------+-------------+-----------------------+----------------+----------------------+---------------+---------------------+----------------+-------------------------+-------------------+--------------+--------------+--+--+--+

最佳答案

我们可以一次完成吗？

create procedure [dbo].[dedup_and_calc] (
    @startMonth int
  , @endMonth int
  , @tvp dbo.tvp readonly 
) as
begin;
  set nocount on;

  declare @date date = (
    select eomonth(max(checkdate))
    from @tvp
    where month(checkdate) >= @startmonth 
      and month(checkdate) <= @endmonth
      );
  /* using row_number() to zero out using rn > 1 in query below */
  ;with cte as (
    select *
     , rn = row_number() over (
         partition by CheckDate, PropNum, PropSeqNum, PropName, ProductionMonth, Product
         order by Header /* There is no Id from @tvp ?*/
         ) 
         /* artifically increase rownumber for Product >= 400 */
         + case when Product >= 400 then 1 else 0 end 
    from @tvp
  )
  insert into RawDataTable(
      CheckDate
    , PropNum
    , PropSeqNum
    , PropName
    , ProductionMonth
    , Product
    , LeaseVolume
    , Price
    , LeaseGrossValue
    , LeaseTaxes
    , LeaseOtherDeductions
    , LeaseNetValue
    , DisbursementDecimal
    , InterestType
    , InterestGrossValue
    , InterestTaxes
    , IntrestOtherDeductions
    , InterestNetValue
  )
  select
       CheckDate             
     , PropNum
     , PropSeqNum            
     , PropName
     , ProductionMonth       
     , Product
     , LeaseVolume           = case when rn =1 then LeaseVolume else 0 end
     , Price                 = case when rn =1 then Price else 0 end
     , LeaseGrossValue       = case when rn =1 then LeaseGrossValue else 0 end
     , LeaseTaxes            = case when rn =1 then LeaseTaxes else 0 end
     , LeaseOtherDeductions  = case when rn =1 then LeaseOtherDeductions else 0 end
     , LeaseNetValue         = case when rn =1 then LeaseNetValue else 0 end
     , DisbursementDecimal   
     , InterestType          
     , InterestGrossValue    = InterestGrossValue
     , InterestTaxes         = InterestTaxes
     , IntrestOtherDeductions= IntrestOtherDeductions
     , InterestNetValue      = InterestNetValue
    from cte 
    where month(CheckDate) >= @startMonth 
      and month(CheckDate) <= @endMonth
  union all 
  select
     CheckDate             = @date
   , PropNum
   , PropSeqNum            = null
   , PropName
   , ProductionMonth       = null
   , Product
   , LeaseVolume           = sum(case when rn =1 then LeaseVolume else null end)
   , Price                 = sum(case when rn =1 then Price else null end)
   , LeaseGrossValue       = sum(case when rn =1 then LeaseGrossValue else null end)
   , LeaseTaxes            = sum(case when rn =1 then LeaseTaxes else null end)
   , LeaseOtherDeductions  = sum(case when rn =1 then LeaseOtherDeductions else null end)
   , LeaseNetValue         = sum(case when rn =1 then LeaseNetValue else null end)
   , DisbursementDecimal   = null
   , InterestType          = null
   , InterestGrossValue    = sum(InterestGrossValue)
   , InterestTaxes         = sum(InterestTaxes)
   , IntrestOtherDeductions= sum(IntrestOtherDeductions)
   , InterestNetValue      = sum(InterestNetValue)
  from cte 
  where month(CheckDate) >= @startMonth 
    and month(CheckDate) <= @endMonth
  group by PropNum, PropName, Product 
end;
go

测试:

CREATE TABLE [dbo].[RawDataTable] (
[Id]                     INT             IDENTITY (1, 1) NOT NULL,
[CheckDate]              DATE            NULL,
[PropNum]                INT             NOT NULL,
[PropSeqNum]             INT             NULL,
[PropName]               NVARCHAR (100)  NOT NULL,
[ProductionMonth]        INT             NULL,
[Product]                INT             NOT NULL,
[LeaseVolume]            DECIMAL (18, 2) NOT NULL,
[Price]                  DECIMAL (18, 2) NOT NULL,
[LeaseGrossValue]        DECIMAL (18, 2) NOT NULL,
[LeaseTaxes]             DECIMAL (18, 2) NULL,
[LeaseOtherDeductions]   DECIMAL (18, 2) NOT NULL,
[LeaseNetValue]          DECIMAL (18, 2) NOT NULL,
[DisbursementDecimal]    DECIMAL (18)    NULL,
[InterestType]           NVARCHAR (10)   NULL,
[InterestGrossValue]     DECIMAL (18, 2) NOT NULL,
[InterestTaxes]          DECIMAL (18, 2) NOT NULL,
[IntrestOtherDeductions] DECIMAL (18, 2) NOT NULL,
[InterestNetValue]       DECIMAL (18, 2) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
go
create type dbo.tvp as table (
    Header                  int
  , OwnerNumber             int
  , CheckNum                varchar(32)
  , CheckDate               date
  , PropNum                 int
  , PropSeqNum              int
  , PropName                varchar(64)
  , ProductionMonth         int
  , Product                 int
  , LeaseVolume             decimal (18, 2)
  , Price                   decimal (18, 2)
  , LeaseGrossValue         decimal (18, 2)
  , LeaseTaxes              decimal (18, 2)
  , LeaseOtherDeductions    decimal (18, 2)
  , LeaseNetValue           decimal (18, 2)
  , DisbursementDecimal     decimal (18, 2)
  , InterestType            varchar(10)
  , InterestGrossValue      decimal (18, 2)
  , InterestTaxes           decimal (18, 2)
  , IntrestOtherDeductions  decimal (18, 2)
  , InterestNetValue        decimal (18, 2)
  --, RecordCount             decimal (18, 2)
  --, CheckAmount             decimal (18, 2)
)

go
create procedure [dbo].[dedup_and_calc] (
    @startMonth int
  , @endMonth int
  , @tvp dbo.tvp readonly 
) as
begin;
  set nocount on;
  declare @date date = (
    select NextMonthStart = dateadd(day,-1,dateadd(month, datediff(month, 0,max(checkdate))+1, 0))
    from @tvp
    where month(checkdate) >= @startmonth 
      and month(checkdate) <= @endmonth
      );
  /* using row_number() to zero out using rn > 1 in query below */
  ;with cte as (
    select *
     , rn = row_number() over (
         partition by CheckDate, PropNum, PropSeqNum, PropName, ProductionMonth, Product
         order by Header
         ) 
         /* artifically increase rownumber for Product >= 400 */
         + case when Product >= 400 then 1 else 0 end 
    from @tvp
  )
  insert into RawDataTable(
      CheckDate
    , PropNum
    , PropSeqNum
    , PropName
    , ProductionMonth
    , Product
    , LeaseVolume
    , Price
    , LeaseGrossValue
    , LeaseTaxes
    , LeaseOtherDeductions
    , LeaseNetValue
    , DisbursementDecimal
    , InterestType
    , InterestGrossValue
    , InterestTaxes
    , IntrestOtherDeductions
    , InterestNetValue
  )
  select
       CheckDate             
     , PropNum
     , PropSeqNum            
     , PropName
     , ProductionMonth       
     , Product
     , LeaseVolume           = case when rn =1 then LeaseVolume else 0 end
     , Price                 = case when rn =1 then Price else 0 end
     , LeaseGrossValue       = case when rn =1 then LeaseGrossValue else 0 end
     , LeaseTaxes            = case when rn =1 then LeaseTaxes else 0 end
     , LeaseOtherDeductions  = case when rn =1 then LeaseOtherDeductions else 0 end
     , LeaseNetValue         = case when rn =1 then LeaseNetValue else 0 end
     , DisbursementDecimal   
     , InterestType          
     , InterestGrossValue    = InterestGrossValue
     , InterestTaxes         = InterestTaxes
     , IntrestOtherDeductions= IntrestOtherDeductions
     , InterestNetValue      = InterestNetValue
    from cte 
    where month(CheckDate) >= @startMonth 
      and month(CheckDate) <= @endMonth
  union all 
  select
     CheckDate             = @date
   , PropNum
   , PropSeqNum            = null
   , PropName
   , ProductionMonth       = null
   , Product
   , LeaseVolume           = sum(case when rn =1 then LeaseVolume else null end)
   , Price                 = sum(case when rn =1 then Price else null end)
   , LeaseGrossValue       = sum(case when rn =1 then LeaseGrossValue else null end)
   , LeaseTaxes            = sum(case when rn =1 then LeaseTaxes else null end)
   , LeaseOtherDeductions  = sum(case when rn =1 then LeaseOtherDeductions else null end)
   , LeaseNetValue         = sum(case when rn =1 then LeaseNetValue else null end)
   , DisbursementDecimal   = null
   , InterestType          = null
   , InterestGrossValue    = sum(InterestGrossValue)
   , InterestTaxes         = sum(InterestTaxes)
   , IntrestOtherDeductions= sum(IntrestOtherDeductions)
   , InterestNetValue      = sum(InterestNetValue)
  from cte 
  where month(CheckDate) >= @startMonth 
    and month(CheckDate) <= @endMonth
  group by PropNum, PropName, Product 

end;
go
declare @tvp dbo.tvp
insert into @tvp values
  ('1','13280701','E008613928','1/25/2015','100004','25','BEAVER LODGE DEVON UT TR-0025','122014','100','774.96','51.93','40243.64','-4628.03','0','35615.61','0.0026932','RI 01','108.38','-12.46','0','95.92')
, ('1','13280701','E008613928','1/25/2015','100004','25','BEAVER LODGE DEVON UT TR-0025','122014','100','774.96','51.93','40243.64','-4628.03','0','35615.61','0.0050669','RI 02','203.91','-23.45','0','180.46')
exec [dbo].[dedup_and_calc] 1,3, @tvp 
select * from RawDataTable as rdt

drop table rawdatatable
drop procedure dedup_and_calc
drop type dbo.tvp

+----+------------+---------+------------+-------------------------------+-----------------+---------+-------------+-------+-----------------+------------+----------------------+---------------+---------------------+--------------+--------------------+---------------+------------------------+------------------+
| Id | CheckDate  | PropNum | PropSeqNum |           PropName            | ProductionMonth | Product | LeaseVolume | Price | LeaseGrossValue | LeaseTaxes | LeaseOtherDeductions | LeaseNetValue | DisbursementDecimal | InterestType | InterestGrossValue | InterestTaxes | IntrestOtherDeductions | InterestNetValue |
+----+------------+---------+------------+-------------------------------+-----------------+---------+-------------+-------+-----------------+------------+----------------------+---------------+---------------------+--------------+--------------------+---------------+------------------------+------------------+
|  1 | 2015-01-25 |  100004 | 25         | BEAVER LODGE DEVON UT TR-0025 | 122014          |     100 | 774.96      | 51.93 | 40243.64        | -4628.03   | 0.00                 | 35615.61      | 0                   | RI 01        | 108.38             | -12.46        | 0.00                   | 95.92            |
|  2 | 2015-01-25 |  100004 | 25         | BEAVER LODGE DEVON UT TR-0025 | 122014          |     100 | 0.00        | 0.00  | 0.00            | 0.00       | 0.00                 | 0.00          | 0                   | RI 02        | 203.91             | -23.45        | 0.00                   | 180.46           |
|  3 | 2015-01-31 |  100004 | NULL       | BEAVER LODGE DEVON UT TR-0025 | NULL            |     100 | 774.96      | 51.93 | 40243.64        | -4628.03   | 0.00                 | 35615.61      | NULL                | NULL         | 312.29             | -35.91        | 0.00                   | 276.38           |
+----+------------+---------+------------+-------------------------------+-----------------+---------+-------------+-------+-----------------+------------+----------------------+---------------+---------------------+--------------+--------------------+---------------+------------------------+------------------+

关于c# - SQL 查询正在复制行并在我的聚合函数中使用这些行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/42353475/

文章推荐： c# - Domain Admins 组的 IsUserInRole 问题(不是配置问题)

文章推荐： java - 无法获取请求 token 方法 POST 必须具有请求正文

文章推荐： android - 如何将音频文件上传到 Parse.com - Android

详解C语言sscanf()函数、vsscanf()函数、vscanf()函数
C语言sscanf()函数：从字符串中读取指定格式的数据头文件： ?
php - 如何解释at()函数； substr()函数;伪代码中的exist()函数
最近，我有一个关于工作预评估的问题，即使查询了每个功能的工作原理，我也不知道如何解决。这是一个伪代码。下面是一个名为foo()的函数，该函数将被传递一个值并返回一个值。如果将以下值传递给foo函数，
VBS教程：函数-CStr 函数
CStr 函数返回表达式，该表达式已被转换为 String 子类型的 Variant。 CStr(expression) expression 参数是任意有效的表达式。说明通常，可以
VBS教程：函数-CSng 函数
CSng 函数返回表达式，该表达式已被转换为 Single 子类型的 Variant。 CSng(expression) expression 参数是任意有效的表达式。说明通常，可
VBS教程：函数-CreateObject 函数
CreateObject 函数创建并返回对 Automation 对象的引用。 CreateObject(servername.typename [, location]) 参数 serv
VBS教程：函数-Cos 函数
Cos 函数返回某个角的余弦值。 Cos(number) number 参数可以是任何将某个角表示为弧度的有效数值表达式。说明 Cos 函数取某个角并返回直角三角形两边的比值。此比值是
VBS教程：函数-CLng 函数
CLng 函数返回表达式，此表达式已被转换为 Long 子类型的 Variant。 CLng(expression) expression 参数是任意有效的表达式。说明通常，您可以使
VBS教程：函数-CInt 函数
CInt 函数返回表达式，此表达式已被转换为 Integer 子类型的 Variant。 CInt(expression) expression 参数是任意有效的表达式。说明通常，可
VBS教程：函数-Chr 函数
Chr 函数返回与指定的 ANSI 字符代码相对应的字符。 Chr(charcode) charcode 参数是可以标识字符的数字。说明从 0 到 31 的数字表示标准的不可打印的
VBS教程：函数-CDbl 函数
CDbl 函数返回表达式，此表达式已被转换为 Double 子类型的 Variant。 CDbl(expression) expression 参数是任意有效的表达式。说明通常，您可
VBS教程：函数-CDate 函数
CDate 函数返回表达式，此表达式已被转换为 Date 子类型的 Variant。 CDate(date) date 参数是任意有效的日期表达式。说明 IsDate 函数用于判断 d
VBS教程：函数-CCur 函数
CCur 函数返回表达式，此表达式已被转换为 Currency 子类型的 Variant。 CCur(expression) expression 参数是任意有效的表达式。说明通常，
VBS教程：函数-CByte 函数
CByte 函数返回表达式，此表达式已被转换为 Byte 子类型的 Variant。 CByte(expression) expression 参数是任意有效的表达式。说明通常，可以
VBS教程：函数-CBool 函数
CBool 函数返回表达式，此表达式已转换为 Boolean 子类型的 Variant。 CBool(expression) expression 是任意有效的表达式。说明如果 ex
VBS教程：函数-Atn 函数
Atn 函数返回数值的反正切值。 Atn(number) number 参数可以是任意有效的数值表达式。说明 Atn 函数计算直角三角形两个边的比值 (number) 并返回对应角的弧
VBS教程：函数-Asc 函数
Asc 函数返回与字符串的第一个字母对应的 ANSI 字符代码。 Asc(string) string 参数是任意有效的字符串表达式。如果 string 参数未包含字符，则将发生运行时错误。
VBS教程：函数-Array 函数
Array 函数返回包含数组的 Variant。 Array(arglist) arglist 参数是赋给包含在 Variant 中的数组元素的值的列表（用逗号分隔）。如果没有指定此参数，则
VBS教程：函数-Abs 函数
Abs 函数返回数字的绝对值。 Abs(number) number 参数可以是任意有效的数值表达式。如果 number 包含 Null，则返回 Null；如果是未初始化变量，则返回 0。
VBS教程：函数-FormatPercent 函数
FormatPercent 函数返回表达式，此表达式已被格式化为尾随有 % 符号的百分比（乘以 100 ）。 FormatPercent(expression[,NumDigitsAfterD
VBS教程：函数-FormatNumber 函数
FormatNumber 函数返回表达式，此表达式已被格式化为数值。 FormatNumber( expression [,NumDigitsAfterDecimal [,Inc

太空宇宙

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

c# - SQL 查询正在复制行并在我的聚合函数中使用这些行