我可以与HAVING子句一起使用的FIRST聚合函数 [英] FIRST aggregate function which I can use with HAVING clause

问题描述

我有一个怪异的要求，需要在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）; 记录的<$ c $值为1 c> 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

这篇关于我可以与HAVING子句一起使用的FIRST聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！