计算分组后组内最大值
【问题】
sql maximum record per group question
CREATE
TABLE DBO.TEST
(
ID INT, RECTYPE INT,SEQ INT, MAX0 INT,MAX1 INT,MAX2 INT)
INSERT
INTO dbo.TEST
SELECT
1,1,1,3,2,3
UNION
ALL
SELECT
1,2,2,3,2,3
UNION
ALL
SELECT
1,2,3,3,2,3
UNION
ALL
SELECT
1,1,2,3,2,3
--SELECT * FROM dbo.TEST
how
to find MAX seq for each ID and Rectype
My result should be
MAX0
is maximum of seq groupby ID
MAX1
is maximum of seq groupby ID where rectype =1
MAX2 id maximum
of seq groupby ID where rectype = 2
ID Rectype SEQ MAX0 MAX1 MAX2
1 1 1 3 2 3
1 2 2 3 2 3
1 2 3 3 2 3
1 1 2 3 2 3
别人的回答：
CREATETABLE DBO.TEST
(
ID INT,
RECTYPE INT,
SEQ INT,
MAX0 INT,
MAX1 INT,
MAX2 INT
)
INSERTINTO dbo.TEST
SELECT1,1,1,NULL,NULL,NULL
UNIONALL
SELECT1,2,2,NULL,NULL,NULL
UNIONALL
SELECT1,2,3,NULL,NULL,NULL
UNIONALL
SELECT1,1,2,NULL,NULL,NULL
--select * from test
;WITH mycte
AS(SELECT ID,
RECTYPE,
Max(seq)
OVER(partition BY ID) m0,
CASE
WHENrectype =1THEN Max(SEQ)OVER(PARTITION BY id, rectype)
ELSENULL
END m1,
CASE
WHENrectype =2THEN Max(SEQ)OVER(PARTITION BY id, rectype)
ELSENULL
END m2
FROM dbo.TEST)
SELECT ID,
RECTYPE,
M0,
Max(m1)OVER(partition BY ID) m1,
Max(m2)
OVER(partition BY ID) m2
FROM mycte
droptable test
【回答】
这是个比较典型的组内计算，解决思路很清晰：
1. 将数据按 ID 分成多个组，每个组是一个 ID 的全部数据。
2. 进行组内运算，求得本组内 SEQ 的最大值，赋给 MAX0。
3. 组内运算，过滤出本组内 Rectype=1 的记录，再求 SEQ 的最大值，赋给 MAX1。
4. 组内运算，过滤出本组内 Rectype=2 的记录，再求 SEQ 的最大值，赋给 MAX2。
上述思路虽然清晰，但用 SQL 却很难表达组内运算，只能转化成 N 个窗口函数嵌套多级关联。这样的代码复杂难懂，下次遇到类似的问题恐怕还是不会写。如果数据量不是非常大时，建议采用 SPL 来辅助。SPL 可以方便地表达组内运算，可以很容易解决你的问题，代码如下：
A |
|
1 |
=tbData.group(ID) |
2 |
=A1.run(~.run(MAX0=A1.~.max(SEQ))) |
3 |
=A1.run(~.run(MAX1= A1.~.select(Rectype==1).max(SEQ))) |
4 |
=A1. run(~.run(MAX2= A1.~.select(Rectype==2).max(SEQ))) |
=A1.run(~.run(MAX0=A1.~.max(SEQ)),
~.run(MAX1=A1.~.select(Rectype==1).max(SEQ)),
~.run(MAX2=A1.~.select(Rectype==2).max(SEQ)) )
上述计算结果是 ResultSet 类型，和 JAVA 或报表都很容易集成，可以参看：
