假设有A,B,C共3个字段,现在存在以下数据
A,B,C
1,xx,2
1,xx,1
2,xxx,3
2,xxx,1
3,x,1A列是有重复的,现在我要按B列分组,每组中A列取最大的那个(即MAX(A)),C列取此时A所对应的那行C(可能不是最大的C)。
该如何写SQL语句?目标数据:
A,B,C
1,xx,2
2,xxx,3
3,x,1
A,B,C
1,xx,2
1,xx,1
2,xxx,3
2,xxx,1
3,x,1A列是有重复的,现在我要按B列分组,每组中A列取最大的那个(即MAX(A)),C列取此时A所对应的那行C(可能不是最大的C)。
该如何写SQL语句?目标数据:
A,B,C
1,xx,2
2,xxx,3
3,x,1
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1'--目标数据
select '1','xx','2' union all
select '2','xxx','1' union all
select '3','x','1'另外回复qianjin036a:你的不对。
回复zy112429,你的也不对。分组后只有3行,你们的结果都不止3行了。
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1'select * from @tb t where not exists(select 1 from @tb where b=t.b and (a>t.a or a=t.a and c>t.c))
/**
A B C
----------- ---------- -----------
1 xx 2
2 xxx 2
3 x 1(3 行受影响)
**/
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '3','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1'
--目标数据
select '3','xx','2' union all
select '2','xxx','1' union all
select '3','x','1'
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1'select a,b,c
from
(
select *,row_number() over(partition by b order by a desc) as RowNo
from @tb
) as t
where RowNo=1/*结果集
a b c
----------- ---------- -----------
3 x 1
1 xx 2
2 xxx 1
*/
1,xx,1还是没看懂,上面两行为什么结果是这个
select '1','xx','2'
xx都一样,max(A)都是1,c列为什么一定是2,不是1
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '2','xx','1' union all
select '3','xx','2' union all
select '4','xxx','1' union all
select '5','xxx','2' union all
select '6','x','1'
--目标数据
select '3','xx','2' union all
select '5','xxx','2' union all
select '6','x','1'
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '2','xx','2' union all
select '2','xx','1' union all
select '4','xxx','1' union all
select '4','xxx','3' union all
select '2','xxx','2' union all
select '3','x','1'select max(a),b,max(c) from @tb where a in (select max(a)from @tb group by b ) group by b
12L的是2005,2000如上,你试下
你能说说--目标数据
select '3','xx','2' union all
select '2','xxx','1' union all
select '3','x','1'为什么a取3,而c取2吗?如果看这结果,就是select MAX(A),MAX(C) FROM TB GROUP BY B
未交待清楚相同B列相同A列时的C的取值
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '3','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1';with fly as
(select *,rn=row_number() over(order by getdate()) from @tb)
select a,b,c
from fly t
where not exists(select 1 from fly where b=t.b and (a>t.a or a=t.a and rn<t.rn))/**
a b c
----------- ---------- -----------
3 xx 1
2 xxx 1
3 x 1(3 行受影响)**/
现在不考虑A列重复取原数据首行的问题了,请参考15楼。A列取最大时,C列取此时对应的原数据A列的值。
from @tb a
inner join
(select max(A) as A,B
from @tb
group by B) b
where a,a=b.a and a.b=b.b
from @tb a
inner join
(select max(A) as A,B
from @tb
group by B) b
where a.a=b.a and a.b=b.b
where A in (
select Max(A) from @tb
group by B
) 我想还有别的方法。回复cd731107,你连语法都有问题,不过思路是对的,只是繁琐了点。
select a.*
from @tb a
inner join
(select max(A) as A,B
from @tb
group by B) b
on a.a=b.a and a.b=b.b
declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '3','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1';with T As(select Row_Number()over(partition by B order by A desc,c) as id,A,B,C from @tb)
select A,B,C from T where id=1
(6 row(s) affected)
A B C
----------- ---------- -----------
3 x 1
3 xx 1
2 xxx 1(3 row(s) affected)楼主~上边的大牛们都很热心帮你解决问题的哦,稍微注意一下自己的态度哦~~
select max(a),b,c from tb group by b;排序前:
+----+---+---+
| a | b | c |
+----+---+---+
| 1 | a | A |
| 2 | b | B |
| 3 | c | C |
| 4 | d | D |
| 11 | a | A |
| 12 | b | B |
| 13 | c | C |
| 14 | d | D |
+----+---+---+
排序后:
+--------+---+---+
| max(a) | b | c |
+--------+---+---+
| 11 | a | A |
| 12 | b | B |
| 13 | c | C |
| 14 | d | D |
+--------+---+---+
select a,b,c from T t
where exists (
select 1 from (
select to_char(t2.a)||'++++'||max(t2.b) d from T t2 group by t2.a
) t3
where t3.d=to_char(t.a)||'++++'||t.b
)
A B C
1 1 aaaa 1
2 1 bbbb 2
3 2 bbbb 4
4 2 dddd 3
5 3 aaaa 1result:
A B C
1 1 bbbb 2
2 2 dddd 3
3 3 aaaa 1
from table1 t2 group by t2.B
insert into t1 values (1,'x',2)
insert into t1 values (2,'xx',4)
insert into t1 values (2,'xx',6)
insert into t1 values (3,'xxx',2)
insert into t1 values (7,'xxx',3)
insert into t1 values (7,'xxxx',2)
select distinct a,c from t1,(select b,max(a) maxa from t1 group by b)t2 where t1.a=t2.maxa
这种数据表的结构 能实现楼主需求的功能吗??
有如下数据
A, B, C
2, XX, a
2, XX, b
2, XX, c输出 A,B列分别是2, XX没任何争议 请问C列楼主心目的正确的输出是什么 ??
SELECT TB_A.* FROM @tb AS TB_A INNER JOIN (SELECT MAX(A) AS A,B FROM @tb GROUP BY B) AS TB_B
ON TB_A.A=TB_B.A
create table TA
(
a NUMBER(1),
b VARCHAR2(5),
c NUMBER(1)
)
插入数据
insert into ta (A, B, C) values (1, 'xx', 2);insert into ta (A, B, C) values (1, 'xx', 1);insert into ta (A, B, C) values (2, 'xxx', 3);insert into ta (A, B, C) values (2, 'xxx', 1);insert into ta (A, B, C) values (2, 'xxx', 1);insert into ta (A, B, C) values (3, 'x', 1);
查询
select *
from ta
where rowid in
(select max(row_id) as row_id
from ((select a, b, rowid as row_id
from ta
where exists
(select 1
from ((SELECT max(a) as a FROM TA T group by t.b) TA1)
where ta1.a = ta.a)) ta_row)
group by (a, b));
where t1.A = t2.A and t1.B = t2.B
B Varchar2(10),
C Number)--把数据塞进去,这里省略
Select * From
(
Select row_number() over(Partition By tt.B Order By tt.A Desc) row_num,
tt.*
From test_tt tt
Order By tt.A,1
)
Where row_num = 1
Order By A排序的结果如下:
ROW_NUM A B C
1 1 xx 2
2 1 xx 1
1 2 xxx 1
2 2 xxx 3
1 3 x 1
最终结果如下:
ROW_NUM A B C
1 1 xx 2
1 2 xxx 3
1 3 x 1
* 1、按B列分组
* 2、每组中A列取最大的那个值
* 3、取每组中最大A列值对应的那C列值
*/declare @tb table (A int ,B nvarchar(10),C int)
insert into @tb
select '1','xx','2' union all
select '1','xx','1' union all
select '3','xx','1' union all
select '2','xxx','1' union all
select '2','xxx','2' union all
select '3','x','1'select * from @tb f
where (select max(g.A) /*取组中最大的A列数据*/ from @tb g where f.B=g.B /*此时按B列分组*/)=f.A /*找出了f表中每组A列最大的值*/查询结果中一共有四组,每组的A列大值情况及对应的C列值如下所示:
A B C
----------- ---------- -----------
2 xxx 1
2 xxx 2
3 xx 1
3 x 1(4 行受影响)
INSERT INTO @test
SELECT '1','x' ,2 UNION ALL
select 'A','B','C' UNION ALL
SELECT '1','xx','2' UNION ALL
SELECT '1','xx','1' UNION ALL
SELECT '2','xxx','3' UNION ALL
SELECT '2','xxx','1' UNION ALL
SELECT '3','x','1'
SELECT b.idsec,b.c,a.i_id FROM @test b ,
(SELECT idsec, max(id) AS i_id
FROM @test
GROUP BY idsec) AS a
WHERE a.idsec = b.idsec
试试这样!