A B C1 10 100
1 5 1000
1 20 102 15 10
2 45 1
2 45 100筛选的结果应该是A列数据相同时,取B列最大值,假如B列有相同数据,则取C列最大值A B C1 20 102 45 100我的写法是SELECT DISTINCT a.* FROM tb a WHERE (B = (SELECT MAX(B) FROM tb WHERE A= a.A)) AND (C = (SELECT MAX(C) FROM tb WHERE C= a.C)) ORDER BY A结果筛选出来少了很多数据,即使不考虑B列数据相同的问题,用Select * from tb a where not exists(select 1 from tb where A=a.A and B>a.B)来筛选,得出的结果是取B值的时候有部分数据不是取的最大值请教各位高手怎么解决?
1 5 1000
1 20 102 15 10
2 45 1
2 45 100筛选的结果应该是A列数据相同时,取B列最大值,假如B列有相同数据,则取C列最大值A B C1 20 102 45 100我的写法是SELECT DISTINCT a.* FROM tb a WHERE (B = (SELECT MAX(B) FROM tb WHERE A= a.A)) AND (C = (SELECT MAX(C) FROM tb WHERE C= a.C)) ORDER BY A结果筛选出来少了很多数据,即使不考虑B列数据相同的问题,用Select * from tb a where not exists(select 1 from tb where A=a.A and B>a.B)来筛选,得出的结果是取B值的时候有部分数据不是取的最大值请教各位高手怎么解决?
我设的是nvarchar
casdec(18,2)是转换数据类型?执行结果是
将数据类型 nvarchar 转换为 numeric 时出错。
from (
select A,max(B) B
from tb
group by a
) a
left join tb on a.A=tb.A and a.B=tb.B
group by a.A,a.B
insert into @t
select 1 , 10 , 100 union all
select 1 , 5, 1000 union all
select 1 , 20 , 10 union all
select 2 , 15 , 10 union all
select 2 ,45 , 1 union all
select 2 , 45 , 100 Select * from @t t1
where b=(select max(b) from @t t2 where A=t1.A)
and c=(select max(c) from @t where A=t1.A and b=t1.b)
/*
A B C
----------- ----------- -----------
1 20 10
2 45 100(2 行受影响)
*/
insert into @t
select 1 , 10 , 100 union all
select 1 , 5, 1000 union all
select 1 , 20 , 10 union all
select 2 , 15 , 10 union all
select 2 ,45 , 1 union all
select 2 , 45 , 100
select a.A,a.B,max(b.C) C
from (
select A,max(B) B
from @t
group by a
) a
left join @t b on a.A=b.A and a.B=b.B
group by a.A,a.B结果:
A B C
1 20 10
2 45 100
执行时发生:在关键字 'from' 附近有语法错误。
你的数据不是整数、浮点?贴出多点数据看看我的sql逻辑很直观:
先取A里B最大的,再关联C,再取C最大的