英文:
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 ![]()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。


评论