我有一个怪异的要求,需要在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.43What 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.CategoryNameI 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聚合函数
发布评论