当某行具有某个值时,我需要对我的输出表进行分区。我有:
ID|Value|User
-------------
1 |A |Rick
2 |A |Rick
3 |B |Rick
7 |A |Rick
8 |C |Rick
4 |A |Joe
5 |B |Joe
6 |B |Joe
我需要为每个单独的用户对我的表进行分区,当值 = A 时,我需要的是:
Row|ID|Value|User
------------------
1 |1 |A |Rick
1 |2 |A |Rick
2 |3 |B |Rick
1 |7 |A |Rick
2 |8 |C |Rick
1 |4 |A |Joe
2 |5 |B |Joe
3 |6 |B |Joe
每个值都有自己的 ID。
我试过这样的事情:
SELECT ROW_NUMBER() OVER(PARTITION BY User, Value = A ORDER BY ID ASC) AS Row, Value, User FROM...
SELECT ROW_NUMBER() OVER(PARTITION BY User, WHERE Value = A ORDER BY ID ASC) AS Row, Value, User FROM...
etc
但我似乎无法弄清楚如何正确编写它,如果可能的话。
似乎您所追求的只是一个DENSE_RANK
:
DENSE_RANK() OVER (PARTITION BY [User] ORDER BY [Value] ASC) AS [Row]
NoteUSER
是 T-SQL 中的保留关键字。我强烈建议您为您的列选择一个不同的名称,否则您必须分隔标识它。ROW
并且VALUE
当前未被保留,但它们被列为未来关键字,并且也建议避免使用它们。
编辑:似乎,实际上它'A'
是1
,然后也递增。这很混乱,但你可以因此你可以这样做:
SELECT CASE [Value]
WHEN 'A' THEN 1
ELSE ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Value]) -
COUNT(CASE [Value] WHEN 'A' THEN 1 END) OVER (PARTITION BY [User]) + 1
END AS [Row],
V.[Value],
V.[User]
FROM (VALUES ('A', 'Rick'),
('A', 'Rick'),
('B', 'Rick'),
('C', 'Rick'),
('A', 'Joe '),
('B', 'Joe '),
('B', 'Joe ')) V ([Value], [User]);
这假设总是至少有一行带有'A'
.
编辑 2:好的,球门柱又被移动了,现在这就是你所追求的。如果他们再次移动,您将需要修改解决方案:
WITH YourTable AS(
SELECT V.ID,
V.Value,
V.[User]
FROM (VALUES(1,'A','Rick'),
(2,'A','Rick'),
(3,'B','Rick'),
(7,'A','Rick'),
(8,'C','Rick'),
(4,'A','Joe '),
(5,'B','Joe '),
(6,'B','Joe '))V(ID,[Value],[User])),
Grps AS(
SELECT YT.ID,
YT.Value,
YT.[User],
COUNT(CASE YT.[Value] WHEN 'A' THEN 1 END) OVER(PARTITION BY YT.[User] ORDER BY YT.ID) AS Grp
FROM YourTable YT)
SELECT ID,
CASE G.[Value]
WHEN 'A' THEN 1
ELSE ROW_NUMBER() OVER (PARTITION BY G.[User], G.Grp ORDER BY G.[Value]) -
COUNT(CASE G.[Value] WHEN 'A' THEN 1 END) OVER (PARTITION BY G.[User], G.Grp) + 1
END AS [Row],
G.[Value],
G.[User]
FROM Grps G
ORDER BY G.[User], G.ID;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句