不好意思 搞错了 应该是这样例如表t a b c 1 5 a 1 2 g 2 8 d 2 3 g 4 9 b 4 8 d 4 5 c 要找到b最大的那条记录的c以下结果 1 a 2 d 4 b 这才对
就是按a分组 然后看b最大的那条记录对应的c字段的值
具体情况是这样的 t1 字段 a c tid t2 字段 id b t1和t2通过tid=id联合得到上面的t 然后要得到上面的结果
WITH a1 (a,b,c) AS ( SELECT 1, 5,'a' UNION ALL SELECT 1, 2,'g' UNION ALL SELECT 2, 8,'d' UNION ALL SELECT 2, 3,'g' UNION ALL SELECT 4, 9,'b' UNION ALL SELECT 4, 8,'d' UNION ALL SELECT 4, 5,'c' ) ,a2 AS ( SELECT a,MAX(b) b FROM a1 GROUP BY a ) SELECT a1.a,a1.c FROM a1 INNER JOIN a2 ON a1.a=a2.a AND a1.b=a2.b
WITH a1 (a,b,c) AS ( SELECT 1, 5,'a' UNION ALL SELECT 1, 2,'g' UNION ALL SELECT 2, 8,'d' UNION ALL SELECT 2, 3,'g' UNION ALL SELECT 4, 9,'b' UNION ALL SELECT 4, 8,'d' UNION ALL SELECT 4, 5,'c' ) ,a2 AS ( SELECT a,c,ROW_NUMBER() OVER (PARTITION BY a ORDER BY b desc) re FROM a1 ) SELECT a,c FROM a2 WHERE re=1
DECLARE @t table (a int,b int,c varchar(10)) INSERT INTO @t(a,b,c) VALUES(1, 5, 'a'), (1, 2, 'g'), (2, 8, 'd'), (2, 3, 'g'), (4, 9, 'b'), (4 ,8, 'd'), (4, 5, 'c');WITH cte AS ( SELECT a,b,c,ROW_NUMBER() OVER(PARTITION BY a ORDER BY b desc) AS rownum FROM @t ) SELECT * FROM cte WHERE rownum=1
DECLARE @t table (a int,b int,c varchar(10)) INSERT INTO @t(a,b,c) VALUES(1, 5, 'a'), (1, 2, 'g'), (2, 8, 'd'), (2, 3, 'g'), (4, 9, 'b'), (4 ,8, 'd'), (4, 5, 'c'); SELECT a,b,c FROM ( SELECT a,b,c,ROW_NUMBER() OVER(PARTITION BY a ORDER BY b desc) AS rownum FROM @t ) t WHERE rownum=1
select a,max(b) b from t group by a
a b c
1 5 a
1 2 g
2 8 d
2 3 g
4 9 b
4 8 d
4 5 c
要找到b最大的那条记录的c以下结果
1 a
2 d
4 b
这才对
t1 字段 a c tid
t2 字段 id b
t1和t2通过tid=id联合得到上面的t
然后要得到上面的结果
WITH a1 (a,b,c) AS
(
SELECT 1, 5,'a' UNION ALL
SELECT 1, 2,'g' UNION ALL
SELECT 2, 8,'d' UNION ALL
SELECT 2, 3,'g' UNION ALL
SELECT 4, 9,'b' UNION ALL
SELECT 4, 8,'d' UNION ALL
SELECT 4, 5,'c'
)
,a2 AS
(
SELECT a,MAX(b) b FROM a1 GROUP BY a
)
SELECT a1.a,a1.c
FROM a1
INNER JOIN a2 ON a1.a=a2.a AND a1.b=a2.b
WITH a1 (a,b,c) AS
(
SELECT 1, 5,'a' UNION ALL
SELECT 1, 2,'g' UNION ALL
SELECT 2, 8,'d' UNION ALL
SELECT 2, 3,'g' UNION ALL
SELECT 4, 9,'b' UNION ALL
SELECT 4, 8,'d' UNION ALL
SELECT 4, 5,'c'
)
,a2 AS
(
SELECT a,c,ROW_NUMBER() OVER (PARTITION BY a ORDER BY b desc) re
FROM a1
)
SELECT a,c FROM a2 WHERE re=1
INSERT INTO @t(a,b,c)
VALUES(1, 5, 'a'),
(1, 2, 'g'),
(2, 8, 'd'),
(2, 3, 'g'),
(4, 9, 'b'),
(4 ,8, 'd'),
(4, 5, 'c');WITH cte AS
(
SELECT a,b,c,ROW_NUMBER() OVER(PARTITION BY a ORDER BY b desc) AS rownum
FROM @t
)
SELECT * FROM cte WHERE rownum=1
INSERT INTO @t(a,b,c)
VALUES(1, 5, 'a'),
(1, 2, 'g'),
(2, 8, 'd'),
(2, 3, 'g'),
(4, 9, 'b'),
(4 ,8, 'd'),
(4, 5, 'c');
SELECT a,b,c FROM (
SELECT a,b,c,ROW_NUMBER() OVER(PARTITION BY a ORDER BY b desc) AS rownum FROM @t
) t
WHERE rownum=1