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

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

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):

SELECT  
    CustomerCode,
    SUBSTRING(SerialNumber, CHARINDEX('-', SerialNumber) + 1, 6) SerialNumber,
    nbServer,
    ISNULL(nbImaging, 0) nbImaging
FROM 
    Magic
WHERE 
    CS_MagicNUmber <> I_MagicNumber
    OR CS_MagicNUmber IS NULL
    OR I_MagicNUmber IS NULL

Sample of resulting data:

| CustomerNumber | SerialNumber | NBServer | NbImaging |
|:-------------- |:------------ | --------:| ---------:|
| 20032455		 | 808225		| 		 3 | 		 0 |
| 20032455		 | 808224		| 		 3 | 		 0 |
| 20032455		 | 808223		| 		 3 | 		 0 |
| 20032625		 | 805779		| 		12 | 		 7 |
| 20032625		 | 805781		| 		12 | 		 7 |
| 20032625		 | 805778		| 		12 | 		 7 |
| 20032625		 | 805782		| 		12 | 		 7 |
| 20032625		 | 805783		| 		12 | 		 7 |
| 20032625		 | 805785		| 		12 | 		 7 |
| 20032625		 | 805786		| 		12 | 		 7 |
| 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中,这样我可以在不重复表达式的情况下引用它。

WITH T AS
(
SELECT  CustomerCode
       ,ca.SerialNumber
       ,nbServer
       ,isnull(nbImaging,0) nbImaging
       ,ROW_NUMBER() over (partition by CustomerCode ORDER BY ca.SerialNumber) rn
FROM Magic
cross apply (select SUBSTRING(SerialNumber,CHARINDEX('-',SerialNumber)+1,6) SerialNumber) ca
WHERE CS_MagicNUmber <> I_MagicNumber
   OR CS_MagicNUmber IS NULL
   OR I_MagicNUmber IS NULL
)
SELECT *
FROM T
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.

WITH T AS
(
SELECT  CustomerCode
       ,ca.SerialNumber
       ,nbServer
       ,isnull(nbImaging,0) nbImaging
	   ,ROW_NUMBER() over (partition by CustomerCode ORDER BY ca.SerialNumber) rn
FROM Magic
cross apply (select SUBSTRING(SerialNumber,CHARINDEX(&#39;-&#39;,SerialNumber)+1,6) SerialNumber) ca
WHERE CS_MagicNUmber &lt;&gt; I_MagicNumber
   OR CS_MagicNUmber IS NULL
   OR I_MagicNUmber IS NULL
)
SELECT *
FROM T
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:

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子句中使用,你需要将整个查询包装在子查询中:

select *
from (
  select *
  , ROW_NUMBER() over(partition by CustomerCode order by something) AS sort
  from yourtable
 ) x
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:

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:

select *
from (
  select *
  , ROW_NUMBER() over(partition by CustomerCode order by something) AS sort
  from yourtable
 ) x
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
匿名

发表评论

匿名网友

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

确定