相同关键字取时间最大的记录,求SQL

student_2008 2011-06-21 10:58:46

create table A(
`ID` INT ,
`ZD1` varchar(20),
`ZD2` varchar(20),
`ZD3` varchar(20)
primary key (`ID`)
);

INSERT INTO A VALUES(1,'A','2011-05-18','22:56');
INSERT INTO A VALUES(2,'B','2011-04-18','22:56');
INSERT INTO A VALUES(3,'A','2011-06-18','21:56');
INSERT INTO A VALUES(4,'A','2011-06-18','22:56');
INSERT INTO A VALUES(5,'A','2011-06-18','22:50');

ZD1如果有相同的值，取ZD2时间最近的一个,如果ZD2也一样,取ZD3最大的记录
查询最后的结果为
2,B,2011-04-18,22:56
4,A,2011-06-18,22:56

...全文

564 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

minitoy 2011-06-22

打赏
举报

上面写的不对.
建议将zd2和zd3合并,使用timestamp或者datetime类型存储

minitoy 2011-06-22

打赏
举报

select id,zd1,zd2,max(zd3) zd3 from table group by id,zd1,zd2

304的的哥 2011-06-22

打赏
举报



--方法5：

SQL> with t as(

  2       select 1 id,'A' zd1,'2011-05-18' zd2,'22:56' zd3 from dual union all

  3       select 2,'B','2011-04-18','22:56' from dual union all

  4       select 3,'A','2011-06-18','21:56' from dual union all

  5       select 4,'A','2011-06-18','22:56' from dual union all

  6       select 5,'A','2011-06-18','22:50' from dual)

  7  select id,zd1,zd2,zd3 from (

  8  select id,zd1,zd2,zd3,

  9         row_number() over (partition by zd1 order by zd2||zd3 desc) rn

 10  from t)

 11  where rn=1

 12  order by id

 13  /



        ID ZD1 ZD2        ZD3

---------- --- ---------- -----

         2 B   2011-04-18 22:56

         4 A   2011-06-18 22:56

ssqtjffcu 2011-06-21

打赏
举报



select *

  from a t1

 where not exists (select 1

          from a t2

         where t2.zd1 = t1.zd1

           and t2.zd2 || t2.zd3 > t1.zd2 || t1.zd3)





                                     ID ZD1                  ZD2                  ZD3

--------------------------------------- -------------------- -------------------- --------------------

                                      2 B                    2011-04-18           22:56

                                      4 A                    2011-06-18           22:56

tangren 2011-06-21

打赏
举报

--1.

SELECT *

  FROM a

 WHERE NOT EXISTS (SELECT 1

          FROM a b

         WHERE b.zd1 = a.zd1

           AND b.zd2 || b.zd3 > a.zd2 || a.zd3);

--2.

SELECT *

  FROM a

 WHERE (zd1, zd2 || zd3) IN

       (SELECT zd1, MAX(zd2 || zd3) FROM a GROUP BY zd1);



--3.

SELECT *

  FROM a

 WHERE a.zd2 || a.zd3 =

       (SELECT MAX(zd2 || zd3) FROM a b WHERE b.zd1 = a.zd1);



--4.

SELECT *

  FROM a

 WHERE 1 > (SELECT COUNT(*)

              FROM a b

             WHERE b.zd1 = a.zd1

               AND b.zd2 || b.zd3 > a.zd2 || a.zd3);