第一个集合函数，我可以用have子句

I have a weird requirement which I need to use inside my Stored Procedure in SQL Server 2008 R2.

我需要在SQL Server 2008 R2中的存储过程中使用一个奇怪的需求。

I need a FIRST aggregate function which returns the first element of a sequence and I will use that with HAVING clause.

我需要第一个聚合函数它返回序列的第一个元素，我将它与have子句一起使用。

Let me give you an example:

让我给你举个例子:

DECLARE @fooTable AS TABLE(
    ID INT,
    CategoryName NVARCHAR(100),
    Name NVARCHAR(100),
    MinAllow INT,
    Price DECIMAL(18,2)
);

INSERT INTO @fooTable  VALUES(1, 'Cat1', 'Product1', 2, 112.2);
INSERT INTO @fooTable  VALUES(2, 'Cat2', 'Product2', 4, 12.34);
INSERT INTO @fooTable  VALUES(3, 'Cat1', 'Product3', 5, 233.32);
INSERT INTO @fooTable  VALUES(4, 'Cat3', 'Product4', 4, 12.43);
INSERT INTO @fooTable  VALUES(5, 'Cat3', 'Product5', 1, 13.00);

DECLARE @minAllowParam AS INT = 3;

SELECT ft.CategoryName, SUM(ft.Price) FROM @fooTable ft
GROUP BY ft.CategoryName;

As you see, we have a table and some dummy values. Inside the SELECT query, we group the categories together and sum the price of the products up.

如你所见，我们有一个表和一些假值。在SELECT查询中，我们将类别分组，并将产品的价格加起来。

This query returns the following result:

此查询返回以下结果:

CategoryName     TotalPrice
---------------- ----------------
Cat1              345.52
Cat2              12.34
Cat3              25.43

What I need here is something like this:

我需要的是这样的东西:

SELECT ft.CategoryName, SUM(ft.Price) FROM @fooTable ft
GROUP BY ft.CategoryName
HAVING GetFIRST(MinAllow) >= @minAllowParam;

In that our case with a query something like this, we should be able to select following results:

在这样的情况下，对于这样的查询，我们应该能够选择以下结果:

INSERT INTO @fooTable  VALUES(2, 'Cat2', 'Product2', 4, 12.34);
INSERT INTO @fooTable  VALUES(4, 'Cat3', 'Product4', 4, 12.43);
INSERT INTO @fooTable  VALUES(5, 'Cat3', 'Product5', 1, 13.00);

As the INSERT INTO @fooTable VALUES(1, 'Cat1', 'Product1', 2, 112.2); record is the first element of a sequence and has the value of 2 for MinAllow column, Cat1 should be out of scope here. On the other hand, INSERT INTO @fooTable VALUES(5, 'Cat3', 'Product5', 1, 13.00); record has the value of 1 for MinAllow column but is the second element of the sequence. So, Cat3 is safe and can be selected.

当插入到@fooTable值(1，'Cat1'， 'Product1'， 2,112.2);记录是序列的第一个元素，MinAllow列的值为2,Cat1应该不在这里。另一方面，插入@fooTable值(5，'Cat3'， 'Product5'， 1, 13.00);记录对于MinAllow列的值为1，但它是序列的第二个元素。因此，Cat3是安全的，可以选择。

Note: MIN or MAX is not what I am looking for!

注意:MIN或MAX不是我要找的!

I know that this example logically does not make sense but I have a situation which it totally does and hard to explain here on the other hand.

我知道这个例子逻辑上讲不通但是我有一个情况它完全是这样的另一方面很难解释。

Any thoughts?

任何想法吗?

Edit:

编辑:

I am assuming that I can achieve what I want here by creating a CLR User-Defined Aggregate Function but I do not want to do this if there is any other choice

我假设我可以通过创建一个CLR用户定义的聚合函数来达到我想要的效果，但是如果还有其他选择的话，我不想这么做

4 个解决方案

#1

How about

如何

SELECT f1.CategoryName, SUM(f1.Price) 
FROM @fooTable AS f1
INNER JOIN (
    SELECT MinAllow, CategoryName
    FROM (
         SELECT MinAllow, CategoryName, ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY ID) AS m
         FROM @fooTable
    ) AS f
    WHERE m = 1
) AS f2 ON f1.CategoryName = f2.CategoryName
WHERE f2.MinAllow >= @minAllowParam
GROUP BY f1.CategoryName

I know not a very elegant query. Maybe I can tweak it a little if I work on it a little longer!

我不知道一个很优雅的查询。也许我可以稍微调整一下，如果我再多做一点的话!

Edit: Ok the inner most subquery should be unnecessary. This should also work:

编辑:好的，最里面的子查询应该是不必要的。这也应该工作:

SELECT f1.CategoryName, SUM(f1.Price) 
FROM @fooTable AS f1
INNER JOIN (
    SELECT MinAllow, CategoryName, ROW_NUMBER() OVER (PARTITION BY CategoryName ORDER BY ID) AS m
    FROM @fooTable
) AS f2 ON f1.CategoryName = f2.CategoryName
WHERE f2.m = 1 AND f2.MinAllow >= @minAllowParam
GROUP BY f1.CategoryName

#2

UPDATE: a readable query:

更新:一种可读的查询:

SELECT ft.CategoryName, SUM(ft.Price) 
FROM fooTable ft
    cross apply
    (
       select top 1 MinAllow
         from fooTable a
        where a.CategoryName = ft.CategoryName
        order by ID
    ) a
where a.MinAllow >= @minAllowParam
GROUP BY ft.CategoryName;

You might filter categories having first (by id?) MinAllow >= @minAllowParam:

您可以筛选具有优先级的类别(通过id?)MinAllow > = @minAllowParam:

...
inner join 
(
   select 
   -- Add columns you might need
     CategoryName,
     Price
   from
     fooTable
   inner join
   (
     -- First ID in category
     select
       min(id) id
     from
       fooTable
     group by
       CategoryName
   ) firstID
   -- Back to all columns
     ON fooTable.ID = firstID.ID
   -- but only if category sequence starts properly
    AND fooTable.MinAllow >= @minAllowParam
) a
-- Allow MinAllow categories only
  ON fooTable.CategoryName = a.CategoryName

#3

Have a look at ROW_NUMBER() with partitioning:

查看ROW_NUMBER()与分区:

http://msdn.microsoft.com/en-us/library/ms186734.aspx

#4

There are two ways that I know to achieve FIRST aggregate function:

我知道有两种方法可以实现第一个聚合函数:

ROW_NUMBER()

(WHERE ROW_NUMBER_COLUMN = 1)

http://msdn.microsoft.com/en-us/library/ms186734.aspx

http://msdn.microsoft.com/en-us/library/ms186734.aspx

AND

和

SELECT ...., (SELECT TOP 1 FROM ... WHERE (outer table join) ORDER BY SOMETHING) AS [FIRST]
FROM ...

#1