oracle 查询 问题

request 发布于 2013/08/31 00:45
阅读 211
收藏 0

类似这样的  表结构

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

         ID       |       money     |        date  

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

         1        |        123         |         2010/07/09

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

         1        |        223         |         2011/07/09

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

         2        |        133         |         2010/07/09

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

         2        |        453         |         2011/07/09

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

         3        |        123         |         2010/07/09

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

         3        |        123         |         2010/07/09

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

         4        |        123         |         2010/07/09

 如图所示,用一个sql  求出 ID为1和2的,最新一条记录,谢谢啦、

结果如下:

         1        |        223         |         2011/07/09

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

         2        |        453         |         2011/07/09

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

 

加载中
0
Mike_Liu
Mike_Liu

我能想到最快的方法是 采用并集  哈. 提供个想法

SELECT
    R.*
FROM
    (
        SELECT
            ID,MONEY,DATE
        FROM
            TAB
        WHERE
            ID = 1
        ORDER BY DATE DESC
    ) R
WHERE
    ROWNUM = 1
UNION
SELECT
    R.*
FROM
    (
        SELECT
            ID,MONEY,DATE
        FROM
            TAB
        WHERE
            ID = 2
        ORDER BY DATE DESC
    ) R
WHERE
    ROWNUM = 1

request
request
多谢,哈哈。采纳了
0
softsword
softsword
最好用存储过程
request
request
多谢建议。
0
c
chengguowei27

--先找ID为1和2的最大日期,然后根据ID和日期查找

select *
  from tab a,
       (select b.id, max(b.date) last_date
          from tab b
         where b.id in (1, 2)
         group by id) b
  where a.id = b.id
    and a.date = b.last_date

request
request
谢谢,试过了 很不错 哈哈
返回顶部
顶部