A1 A2 A3 A4 A5
482 2011-05-27 1895 1 111111
482 2011-05-27 1894 2 777
482 2011-05-27 1894 1 997
482 2011-05-27 1893 1 99.99
482 2011-05-27 1891 1 99998
2154 2011-05-26 1890 1 1099
2154 2011-05-26 1889 1 9999
2154 2011-05-25 1839 1 100
482 2011-05-25 1887 1 9999
723 2009-12-10 1879 1 0.12
723 2009-12-10 1879 2 0.5 请问如何查询出不同的A1 取A2 最大的值,当A2相同时取A3最大,当A3也相同时取A4 最大的值最后结果是
A1 A2 A3 A4 A5
482 2011-05-27 1895 1 111111
2154 2011-05-26 1890 1 1099
723 2009-12-10 1879 2 0.5
482 2011-05-27 1895 1 111111
482 2011-05-27 1894 2 777
482 2011-05-27 1894 1 997
482 2011-05-27 1893 1 99.99
482 2011-05-27 1891 1 99998
2154 2011-05-26 1890 1 1099
2154 2011-05-26 1889 1 9999
2154 2011-05-25 1839 1 100
482 2011-05-25 1887 1 9999
723 2009-12-10 1879 1 0.12
723 2009-12-10 1879 2 0.5 请问如何查询出不同的A1 取A2 最大的值,当A2相同时取A3最大,当A3也相同时取A4 最大的值最后结果是
A1 A2 A3 A4 A5
482 2011-05-27 1895 1 111111
2154 2011-05-26 1890 1 1099
723 2009-12-10 1879 2 0.5
(
select id= row_number() over (partition by A1 order by A1 , A2, A3, A4 desc),* from ta
)
select * from cte where id =1
(
select id= row_number() over (partition by A1
order by A1 desc,
A2 desc,
A3 desc,
A4 desc),* from ta
)
select * from cte where id =1
set nocount on
if object_id('test1','U') is not null
drop table test1
go
create table test1(A1 INT ,A2 char(10),A3 INT ,A4 INT ,A5 numeric(9,2))
insert into test1
select 482,'2011-05-27',1895,1,111111 union all
select 482,'2011-05-27' ,1894 ,2, 777 union all
select 482,'2011-05-27' ,1894 ,1 ,997 union all
select 482,'2011-05-27' ,1893 ,1 ,99.99 union all
select 482,'2011-05-27' ,1891 ,1, 99998 union all
select 2154,'2011-05-26' ,1890, 1 ,1099 union all
select 2154 ,'2011-05-26' ,1889 ,1 ,9999 union all
select 2154 ,'2011-05-25' ,1839 ,1 ,100 union all
select 482 ,'2011-05-25' ,1887 ,1 ,9999 union all
select 723 ,'2009-12-10' ,1879 ,1 ,0.12 union all
select 723 ,'2009-12-10', 1879 ,2 ,0.5 --SELECt * from @a
SELECT B.A1,B.A2,B.A3,B.A4,B.A5 FROM (SELECT row_number() over(partition by A1 order by A1 DESC,A2 DESC,A3 DESC,A4 DESC ) rk, A1,A2,A3,A4,A5 from test1 )B WHERE B.RK=1---测试结果
A1 A2 A3 A4 A5
----------- ---------- ----------- ----------- ---------------------------------------
482 2011-05-27 1895 1 111111.00
723 2009-12-10 1879 2 0.50
2154 2011-05-26 1890 1 1099.00
在关键字 'WITH' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 1
'row_number' 不是可以识别的 函数名。
(select A1,max(convert(varchar(8),A2, 112) + right('000000' + cast(A3 AS varchar(10)), 6) + right('000000' + cast(A4 AS varchar(10)), 6)) as maxa from t_t group by A1) b
where a.A1 = b.A1
and convert(varchar(8),A2, 112) + right('000000' + cast(A3 AS varchar(10)), 6) + right('000000' + cast(A4 AS varchar(10)), 6) = b.maxa