日期:2014-05-17 浏览次数:20519 次
CREATE TABLE TBA
(
JO002 nvarchar(50),
JO014 nvarchar(50),
JO027 nvarchar(50)
)
INSERT INTO TBA
select 'QBC116','2012/07/20 13:00:00','240' union
select 'QBC116','2012/08/11 08:00','240' union
select 'QBC116','2012/08/11 08:00','180' union
select 'QBC135','2012/07/31 08:00:00','240' union
select 'QBC136','2012/09/08 08:00','2610' union
select 'QBC136','2012/09/10 08:00','1320' union
select 'QBC179','2012/08/23 11:00','360' union
select 'QBC179','2012/08/22 11:00','360' union
select 'QBC197','2012/08/28 10:00','360' union
select 'QBC197','2012/08/28 08:00','360' union
select 'QBC198','2012/09/06 08:00','360' union
select 'QBC198','2012/08/28 08:00','360' union
select 'QZC041','2012/07/31 08:00:00','4758' union
select 'QZC041','2012/06/23 00:00:00','2440.2' union
select 'QZC041','2012/06/24 00:00:00','1219.8' union
select 'QZC042','2012/07/31 08:00:00','4758' union
select 'QZC042','2012/06/29 15:00:00','2440.2' union
select 'QZC042','2012/07/02 08:00:00','1219.8'
WITH tb
AS ( SELECT JO002 ,
MAX(JO027) AS 'JO027'
FROM #TBA
GROUP BY JO002
)
SELECT a.* ,
( SELECT MAX(JO014)
FROM #TBA b
WHERE b.JO027 = a.JO027
AND b.JO002 = a.JO002
) AS 'JO014'
FROM tb a
select
JO002,
MAX(JO014)
from
TBA as A
where
NOT EXISTS(select 1 from TBA where A.JO002=JO002 AND A.JO027<JO027 )
GROUP BY
JO002
select max(A.JO014),A.JO002,B.JO027 from TBA A,
(select max(JO027) as JO027,JO002 from TBA group b