A1 A2 A3 A4 A5
482 2011-05-27 1894 1 997
482 2011-05-27 1894 2 777
2154 2011-05-26 1889 1 9999
2154 2011-05-26 1890 1 1099
2154 2011-05-25 1839 1 100
482 2011-05-25 1887 1 9999
723 2009-12-10 1879 1 0.12
756 2009-12-10 1879 2 0.12
757 2009-12-10 1879 3 0.12 ... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ... 请问如何查询出不同的A1 取A2 最大时的值,当A2相同时取A3最大, 当A3也相同时取A4最大的值最后结果是
A1 A2 A3 A4 A5
482 2011-05-27 1894 2 777
2154 2011-05-26 1890 1 1099
723 2009-12-10 1879 1 0.12
756 2009-12-10 1879 2 0.12
757 2009-12-10 1879 3 0.12
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
from tt
group by A1A5不知道是怎么取的
go
--> -->
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([A1] int,[A2] Datetime,[A3] Datetime,[A4] int,[A5] decimal(18,2))
Insert #1
select 482,'2011-05-27','1894',2,777 union all
select 2154,'2011-05-26','1889',1,9999 union all
select 2154,'2011-05-26','1890',1,1099 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 756,'2009-12-10','1879',2,0.12 union all
select 757,'2009-12-10','1879',3,0.12
Go
Select *
from #1 AS a
WHERE
NOT EXISTS(SELECT 1 FROM #1 WHERE A1=a.A1 AND A2>a.A2 )
AND
NOT EXISTS(SELECT 1 FROM #1 WHERE A1=a.A1 AND A2=a.A2 AND A3>a.A3)
AND
NOT EXISTS(SELECT 1 FROM #1 WHERE A1=a.A1 AND A2=a.A2 AND A3=a.A3 AND A4>a.A4)
go
--> -->
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([A1] int,[A2] Datetime,[A3] Datetime,[A4] int,[A5] decimal(18,2))
Insert #1
select 482,'2011-05-27','1894',2,777 union all
select 2154,'2011-05-26','1889',1,9999 union all
select 2154,'2011-05-26','1890',1,1099 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 756,'2009-12-10','1879',2,0.12 union all
select 757,'2009-12-10','1879',3,0.12
Go
Select *
from #1 AS a
WHERE
CHECKSUM(a.A2,a.A3,a.A4)=(SELECT TOP 1 CHECKSUM(A2,A3,A4) FROM #1 WHERE A1=a.A1 ORDER BY A2 desc,A3 DESC,A4 DESC )
/*A1 A2 A3 A4 A5
482 2011-05-27 00:00:00.000 1894-01-01 00:00:00.000 2 777.00
2154 2011-05-26 00:00:00.000 1890-01-01 00:00:00.000 1 1099.00
723 2009-12-10 00:00:00.000 1879-01-01 00:00:00.000 1 0.12
756 2009-12-10 00:00:00.000 1879-01-01 00:00:00.000 2 0.12
757 2009-12-10 00:00:00.000 1879-01-01 00:00:00.000 3 0.12
*/
有主健時把CHECKSUM換為主健
Insert #1
select 482,'2011-05-27','1894',2,777 union all
select 2154,'2011-05-26','1889',1,9999 union all
select 2154,'2011-05-26','1890',1,1099 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 756,'2009-12-10','1879',2,0.12 union all
select 757,'2009-12-10','1879',3,0.12
Go
select a.* FROM #1 a,
(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 #1 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