我可以与HAVING子句一起使用的FIRST聚合函数

本文介绍了我可以与HAVING子句一起使用的FIRST聚合函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

我需要一个FIRST聚合函数，该函数返回序列的第一个元素，并将其与 HAVING 子句一起使用

让我举个例子：

DECLARE @fooTable如表（ ID INT， CategoryName NVARCHAR（100）， Name NVARCHAR（100）， MinAllow INT，价格DECIMAL（18,2））; 插入@fooTable VALUES（1，'Cat1'，'Product1'，2，112.2）; 插入@fooTable VALUES（2，'Cat2'，'Product2'，4，12.34）; 插入@fooTable VALUES（3，'Cat1'，'Product3'，5，233.32）; 插入@fooTable VALUES（4，'Cat3'，'Product4'，4，12.43）; 插入@fooTable VALUES（5，'Cat3'，'Product5'，1，13.00）; 声明@minAllowParam AS INT = 3; 选择ft.CategoryName，SUM（ft.Price）来自@fooTable ft GROUP BY ft.CategoryName;

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

此查询返回以下结果：

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

我这里需要的是这样的

SELECT ft.CategoryName ，SUM（ft.Price）来自@fooTable ft GROUP BY ft.CategoryName HAVING GetFIRST（MinAllow）> = @ minAllowParam;

在这种情况下，对于类似这样的查询，我们应该能够选择以下结果： / p>

INSERT INTO @fooTable VALUES（2，'Cat2'，'Product2'，4，12.34）; 插入@fooTable VALUES（4，'Cat3'，'Product4'，4，12.43）; 插入@fooTable VALUES（5，'Cat3'，'Product5'，1，13.00）;

作为 INSERT INTO @fooTable VALUES（1，'Cat1'，' Product1'，2，112.2）; 记录是序列的第一个元素，对于 MinAllow 列 Cat1，其值为2 不在此处。另一方面， INSERT INTO @fooTable VALUES（5，'Cat3'，'Product5'，1，13.00）; 记录的 MinAllow 列，但它是序列的第二个元素。因此， Cat3 是安全的并且可以选择。

注意： MIN 或 MAX 不是我想要的！

我知道这个例子从逻辑上讲是没有意义的，但是我有一个完全可以理解的情况，另一方面在这里很难解释。

有什么想法吗？

编辑：

我假设我可以通过创建CLR 用户定义的聚合函数来实现我想要的功能，但是如果还有还有其他选择，我也不想这样做

解决方案

怎么样

SELECT f1.CategoryName， SUM（f1.Price） FROM @fooTable AS f1 INNER JOIN（ SELECT MinAllow，CategoryName FROM（ SELECT MinAllow，CategoryName，ROW_NUMBER（）OVER（分区按CategoryName顺序按ID）AS m FROM @ fooTable ）AS f 其中m = 1 ）AS f2在f1.CategoryName = f2.CategoryName F2.MinAllow> = @minAllowParam GROUP BY f1.CategoryName

我知道这不是一个很好的查询。

Edit ：也许我可以对其稍作调整。

Edit ：好的，最里面的子查询应该是不必要的。这也应该起作用：

SELECT f1.CategoryName，SUM（f1.Price） FROM @fooTable AS f1 内联接（选择MinAllow，CategoryName，ROW_NUMBER（）OVER（按CategoryName排序ID的ID）AS m FROM @fooTable ）AS f2在f1上.CategoryName = f2。 CategoryName 其中f2.m = 1和f2.MinAllow> = @minAllowParam 由f1分组.CategoryName

I have a weird requirement which I need to use inside my Stored Procedure in 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.

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.

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.

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

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

解决方案

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