英文:
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('-',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
答案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 <= nbServer - nbImaging
EDIT: or what @Martin wrote
编辑:或者参考@Martin写的代码
英文:
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 <= nbServer - nbImaging
EDIT: or what @Martin wrote
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论