我在StackOverflow上研究了五个不同的线程,我觉得我越来越接近了,但仍然没有雪茄。 我的原始表有几个部分,每个部分有几行,每行有几个位置。 我希望能够查询一个结果,其中列出了部分、行、位置、行中该位置上方的位置数(位置编号较低(以及行中该位置下方的位置数(位置编号更大(。
这是我所拥有的:
SELECT section,
row,
position AS P,
SUM(case when position < P then 1 else 0 end) AS Above,
SUM(case when position > P then 1 else 0 end) AS Below
FROM OriginalTable
GROUP BY section, row, position
ORDER BY section, row, position
求和情况是不识别 P 作为位置,当我将 P 更改为数字时,总和是相差甚远的。
一种可能的解决方案是使用相关子查询获取部分和行的较低/较高位置的count()
。
SELECT ot1.section,
ot1.row,
ot1.position,
(SELECT count(*)
FROM originaltable ot2
WHERE ot2.section = ot1.section
AND ot2.row = ot2.row
AND ot2.position > ot1.position) above,
(SELECT count(*)
FROM originaltable ot2
WHERE ot2.section = ot1.section
AND ot2.row = ot2.row
AND ot2.position < ot1.position) below
FROM originaltable ot1
ORDER BY ot1.section,
ot1.row,
ot1.position;
另一个是使用窗口版本的count()
。
SELECT ot1.section,
ot1.row,
ot1.position,
count(*) OVER (PARTITION BY ot1.section,
ot1.row
ORDER BY ot1.position
RANGE BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) - 1 above
count(*) OVER (PARTITION BY ot1.section,
ot1.row
ORDER BY ot1.position
RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - 1 below
FROM originaltable ot1
ORDER BY ot1.section,
ot1.row,
ot1.position;
我只会使用row_number()
:
SELECT section, row, position AS P,
ROW_NUMBER() OVER (PARTITION BY section, row ORDER BY position ASC) - 1 as Above,
ROW_NUMBER() OVER (PARTITION BY section, row ORDER BY position DESC) - 1 as Below
FROM OriginalTable
ORDER BY section, row, position;
如果您有重复项,那么问题会更棘手(尽管GROUP BY
会解决这个问题(。 您可能想要DENSE_RANK()
而不是ROW_NUMBER()
。