有个表两列
ID DATE
1 2013-11-01 23:00:00
1 2014-01-30 09:40:00
2 2014-02-01 10:34:45
2 2014-02-02 11:45:00
2 2014-04-05 09:00:01
想得到结果是
ID DATE
1 2014-01-30 09:40:00
2 2014-04-05 09:00:01
求这个SQL语句如何写
------解决思路----------------------
SELECT id,to_char(max(da),'yyyy-mm-dd hh24:mi:ss') da FROM t_t GROUP BY id
date 是关键字 ,不要定义为列名
------解决思路----------------------
select id,max(date) from table group by id;
------解决思路----------------------
根据id进行分组,在每个组中取时间较大的那条记录。
select id,to_char(max(t_date),'yyyy-mm-dd hh24:mi:ss') from tab group by id;
------解决思路----------------------
如数据表列为id,da,则
select id , da from (
select id , da , row_number() over (partition by id order by da desc ) as seq from t_t
) where seq =1
------解决思路----------------------
WITH T_test AS
(SELECT 42723 DWG_ID, 9 REV_ID, '1' REV_NC
FROM DUAL
UNION ALL
SELECT 42723, 7, '0C'
FROM DUAL
UNION ALL
SELECT 42723, 9, '0A'
FROM DUAL
UNION ALL
SELECT 42723, 2, '0'
FROM DUAL
UNION ALL
SELECT 42723, 1, 'L'
FROM DUAL
UNION ALL
SELECT 42723, 8, '0D'
FROM DUAL
UNION ALL
SELECT 42723, 6, '0B'
FROM DUAL
UNION ALL
SELECT 42723, 0, '#'
FROM DUAL
UNION ALL
SELECT 42723, 2, 'M'
FROM DUAL
UNION ALL
SELECT 42723, 3, 'M1'
FROM DUAL
UNION ALL
SELECT 42769, 0, '#'
FROM DUAL)
SELECT D.DWG_ID,D.REV_ID,D.REV_NC FROM
(SELECT A.DWG_ID, A.REV_ID ,A.REV_NC ,row_number() over (partition by A.DWG_ID order by A.REV_ID desc )RN
FROM T_test A,
(SELECT DWG_ID,max(REV_ID)REV_ID FROM T_test GROUP BY DWG_ID)B
WHERE A.DWG_ID=B.DWG_ID AND A.REV_ID=B.REV_ID) D WHERE RN=1
------解决思路----------------------
建议楼主用用分析函数,row_number() over (partition by ID order by date desc ) rn,先使每行有一个序列号,然后取序列号为1的即可
------解决思路----------------------
同上。 还是理解为主。
------解决思路----------------------
如果楼主就这两列,可以直接使用max函数,如果列较多,建议使用row_number()over(partition by id order by date desc),之后取row_number=‘1’,这样可以保留所有的列信息
------解决思路----------------------
@su_think
数据库这块主要只是增删改查(操作基本,一般来说只要不是专注这块的,掌握这基本的就够了),如果要学习的话,那就需要多敲多练习了,主要先从增删改查(包括子查询等等)这块开始、。。然后在学着写存储过程这些,此外还是多看看这块的sql并试着理解起执行顺序吧。。(个人意见)
------解决思路----------------------
select id,to_char(max(t_date),'yyyy-mm-dd hh24:mi:ss') from tab group by id;
------解决思路----------------------
根据ID进行分组,然后去日期的最大值
select id,to_char(max(t_date),'yyyy-mm-dd hh24:mi:ss') from tab group by id;
------解决思路----------------------

------解决思路----------------------
果断选择select id,max(date)
from test
group by id
简单方便实用,我开始也是想用distinct来着后来还是觉得这个方法好用
------解决思路----------------------
如果楼主就这两列,可以直接使用max函数,
如果数据较多使用分析函数,row_number() over (partition by ID order by date desc ) rn,先使每行有一个序列号,然后取序列号为1的即可