SELECT TOP 基于两列相减并按另一列分组的 CTE 中。

huangapple
go评论106阅读模式
英文:

SELECT TOP from CTE based on subtraction of 2 column group by another

问题

我希望过滤那些共享相同 CustomerCode 记录组的结果，即获得 TOP (nbServer - nbImaging)。

在示例中，对于 CustomerCode 为 20032455 的情况，差值为 3 (3-0)，我有 2 行记录。但是对于接下来的 CustomerCode，即 20032625，我希望获取前 5 行记录 (12-7)，即 SerialNumber 为 805779、805782、805781、805778 和 805783 的记录。

当然，实际情况可能更复杂，但我认为这个示例展示了我想要的结果。我已经阅读了多个示例并尝试使用 PARTITION OVER，但是我必须承认，没有一个方法能够实现。

英文:

I have data which is the results from a bunch of CTE and a query and this is where I am at.

Query (not including all previous CTE tables):

  1. SELECT  
  2.     CustomerCode,
  3.     SUBSTRING(SerialNumber, CHARINDEX('-', SerialNumber) + 1, 6) SerialNumber,
  4.     nbServer,
  5.     ISNULL(nbImaging, 0) nbImaging
  6. FROM 
  7.     Magic
  8. WHERE 
  9.     CS_MagicNUmber <> I_MagicNumber
  10.     OR CS_MagicNUmber IS NULL
  11.     OR I_MagicNUmber IS NULL

Sample of resulting data:

  1. | CustomerNumber | SerialNumber | NBServer | NbImaging |
  2. |:-------------- |:------------ | --------:| ---------:|
  3. | 20032455		 | 808225		| 		 3 | 		 0 |
  4. | 20032455		 | 808224		| 		 3 | 		 0 |
  5. | 20032455		 | 808223		| 		 3 | 		 0 |
  6. | 20032625		 | 805779		| 		12 | 		 7 |
  7. | 20032625		 | 805781		| 		12 | 		 7 |
  8. | 20032625		 | 805778		| 		12 | 		 7 |
  9. | 20032625		 | 805782		| 		12 | 		 7 |
  10. | 20032625		 | 805783		| 		12 | 		 7 |
  11. | 20032625		 | 805785		| 		12 | 		 7 |
  12. | 20032625		 | 805786		| 		12 | 		 7 |
  13. | 20032625		 | 805780		| 		12 | 		 7 |

What I want is to filter those results for each group of records sharing the same CustomerCode I.E. to get the TOP (nbServer - nbImaging).

In the sample shown, for CustomerCode 20032455 is it simple as the difference is 3 (3-0) and I have 2 rows. But for the following CustomerCode, 20032625, I want the 5 first rows (12-7), so those with SerialNumber 805779, 805782, 805781, 805778, 805783 .

Of course there are a lot more but I think that shows what I want as a result. I have read multiple examples and also try PARTITION OVER but I must confess, nothing works.

答案1

得分: 0

这很容易使用ROW_NUMBER，因为它只会给你升序的连续整数从1，你可以在WHERE子句中使用它。

我还将SerialNumber的计算放入CROSS APPLY中，这样我可以在不重复表达式的情况下引用它。

  1. WITH T AS
  2. (
  3. SELECT  CustomerCode
  4.        ,ca.SerialNumber
  5.        ,nbServer
  6.        ,isnull(nbImaging,0) nbImaging
  7.        ,ROW_NUMBER() over (partition by CustomerCode ORDER BY ca.SerialNumber) rn
  8. FROM Magic
  9. cross apply (select SUBSTRING(SerialNumber,CHARINDEX('-',SerialNumber)+1,6) SerialNumber) ca
  10. WHERE CS_MagicNUmber <> I_MagicNumber
  11.    OR CS_MagicNUmber IS NULL
  12.    OR I_MagicNUmber IS NULL
  13. )
  14. SELECT *
  15. FROM T
  16. WHERE rn <= nbServer - nbImaging
英文:

This is relatively straight forward with ROW_NUMBER as that just gives you ascending sequential integers from 1 that you can use in a WHERE clause.

I do also tuck the calculation of SerialNumber into a CROSS APPLY so I can reference it twice without repeating the expression.

  1. WITH T AS
  2. (
  3. SELECT  CustomerCode
  4.        ,ca.SerialNumber
  5.        ,nbServer
  6.        ,isnull(nbImaging,0) nbImaging
  7. 	   ,ROW_NUMBER() over (partition by CustomerCode ORDER BY ca.SerialNumber) rn
  8. FROM Magic
  9. cross apply (select SUBSTRING(SerialNumber,CHARINDEX(&#39;-&#39;,SerialNumber)+1,6) SerialNumber) ca
  10. WHERE CS_MagicNUmber &lt;&gt; I_MagicNumber
  11.    OR CS_MagicNUmber IS NULL
  12.    OR I_MagicNUmber IS NULL
  13. )
  14. SELECT *
  15. FROM T
  16. WHERE rn &lt;= nbServer - nbImaging

答案2

得分: 0

I don't understand why partition doesn't work for you.
你不理解为什么分区对你不起作用。

You want to count up a group of values, this is done by row_number:
你想对一组值进行计数，可以使用row_number：

  1. select ROW_NUMBER() over (partition by CustomerCode order by something) AS sort

Now, you want to only get the first X records from each group. Since ROW_NUMBER cannot be in a WHERE, you need to wrap the whole thing in a subquery:

现在，你想要从每个分组中只获取前X条记录。由于ROW_NUMBER不能在WHERE子句中使用，你需要将整个查询包装在子查询中：

  1. select *
  2. from (
  3.   select *
  4.   , ROW_NUMBER() over(partition by CustomerCode order by something) AS sort
  5.   from yourtable
  6.  ) x
  7. where sort &lt;= nbServer - nbImaging

EDIT: or what @Martin wrote SELECT TOP 基于两列相减并按另一列分组的 CTE 中。
编辑：或者参考@Martin写的代码 SELECT TOP 基于两列相减并按另一列分组的 CTE 中。

英文:

I don't understand why partition doesn't work for you.
You want to count up a group of values, this is done by row_number:

  1. select ROW_NUMBER()
  2. over (partition by CustomerCode
  3. order by something) AS sort

Now, you want to only get the first X records from each group. Since ROW_NUMBER cannot be in a WHERE, you need to wrap the whole thing in a subquery:

  1. select *
  2. from (
  3.   select *
  4.   , ROW_NUMBER() over(partition by CustomerCode order by something) AS sort
  5.   from yourtable
  6.  ) x
  7. where sort &lt;= nbServer - nbImaging

EDIT: or what @Martin wrote SELECT TOP 基于两列相减并按另一列分组的 CTE 中。

huangapple
  • 本文由 发表于 2023年3月4日 05:06:49
  • 转载请务必保留本文链接：https://go.coder-hub.com/75631876.html
  • filter
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定