解决方案 »
- 如何将Clob类型改成Varchar类型?
- 这段sql怎么优化?
- 弱弱的问题.... in和exists =.=!!
- 关于外连接的问题,高手帮忙!
- 从Oracle官方直接下载的Oracle 9i 和 Oracle 10 是中文版本吗??
- 推荐几本oracle专家级的经典外文翻译书
- 菜鸟问题:CHAR类型字段,存的数字,相加,结果不是串连接,而是把数字相加结果返回了?
- 请帮忙看看这个SQL 语句有什么问题?
- 【help】怎样能取得数据库里当前最大的id号?怎样取得一段时间内新加入的数据?
- 关于存储过程! 高分求助!
- Oracle 本地文件的写出问题
- ORA-02046: distributed transaction already begun
select * from 表B
union
select LIAD as LID,CUSID,LCUSPLANID,lfilldate from 表A 我这样查出来的结果,却不知怎么仅显示最新的一条,求助啊
tbl_a AS
(SELECT 'LS0000077' LID, 'C093' CUSID, 'CL0000054' LCUSPLANID, '2009-09-25 17:23:36' lfilldate
FROM dual UNION ALL
SELECT 'LS0000080' LID, 'C093' CUSID, 'CL0000054' LCUSPLANID, '2009-09-26 17:23:36' lfilldate
FROM dual),
tbl_b AS
(SELECT 'LS0000078' LID, 'C093' CUSID, 'CL0000054' LCUSPLANID, '2009-09-24 17:23:36' lfilldate
FROM dual UNION ALL
SELECT 'LS0000079' LID, 'C094' CUSID, 'CL0000066' LCUSPLANID, '2009-09-24 18:23:36' lfilldate
FROM dual)
SELECT TBL_B.LID,
TBL_B.CUSID,
TBL_B.LCUSPLANID,
CASE
WHEN TBL_A.LFILLDATE > TBL_B.LFILLDATE THEN
TBL_A.LFILLDATE
ELSE
TBL_B.LFILLDATE
END LFILLDATE
FROM TBL_B,
(SELECT LCUSPLANID, MAX(LFILLDATE) LFILLDATE
FROM TBL_A
GROUP BY LCUSPLANID) TBL_A
WHERE TBL_B.LCUSPLANID = TBL_A.LCUSPLANID(+)
SELECT * FROM ab WHERE liad IN (SELECT FIRST_VALUE(liad) OVER(PARTITION BY lcusplanid ORDER BY lfilldate DESC) FROM ab);
set lfilldate=m.lfilldate
from (select lcusplanid,max(lfilldate) lfilldate from 表A) m
where lcusplanid=m.lcusplanid
还有点问题:
1.如果表A中存在与表B相同LCUSPLANID,则表A的日期一定会比表B中的大
2.比较的是取表A中时间最大的那整条记录,替换B表中的那条记录,并不是仅交换时间
set lfilldate=(select max(lfilldate) from 表A where 表B.lcusplanid=lcusplanid )update 表B
set lfilldate=m.lfilldate
from (select lcusplanid,max(lfilldate) lfilldate from 表A) m
where lcusplanid=m.lcusplanid
这两个应该有一个可以用
-------------------- ---------- -------------------- --------------------
LS0077 1 CL0054 2009-09-25 17:00:00
LS0080 2 CL0054 2009-09-26 17:00:00已用时间: 00: 00: 00.00
16:21:52 scott@TUNGKONG> SELECT * FROM b;LIAD CUSID LCUSPLANID LFILLDATE
-------------------- ---------- -------------------- --------------------
LS0078 3 CL0054 2009-09-24 17:00:00
LS0079 4 CL0066 2009-09-24 17:00:00已用时间: 00: 00: 00.01
16:21:55 scott@TUNGKONG> WITH ab AS (SELECT lid liad,cusid,lcusplanid,lfilldate FROM a UNION SELECT liad,cusid,lcusplanid,lfilldate FROM b)
16:21:58 2 SELECT * FROM ab WHERE liad IN (SELECT FIRST_VALUE(liad) OVER(PARTITION BY lcusplanid ORDER BY lfilldate DESC) FROM ab);LIAD CUSID LCUSPLANID LFILLDATE
-------------------- ---------- -------------------- --------------------
LS0079 4 CL0066 2009-09-24 17:00:00
LS0080 2 CL0054 2009-09-26 17:00:00已用时间: 00: 00: 00.01
SQL> select * from a;LID CUSID LCUSPLANID LFILLDATE
---------- -------- ---------- -----------
LS0000077 C093 CL0000054 9/25/2009 5
LS0000080 C093 CL0000054 9/26/2009 5SQL> select * from b;LIAD CUSID LCUSPLANID LFILLDATE
---------- -------- ---------- -----------
LS0000078 C093 CL0000054 9/24/2009 5
LS0000079 C094 CL0000056 9/24/2009 6SQL>
SQL> select liad,cusid,LCUSPLANID,to_char(LFILLDATE,'yyyy-mm-dd hh24:mi:ss') from (
2 select liad,cusid,LCUSPLANID,LFILLDATE,row_number()over(partition by cusid,LCUSPLANID order by LFILLDATE desc) rn from (
3 select * from b where not exists(
4 select 1 from a where a.LCUSPLANID=b.Lcusplanid
5 )
6 union all
7 select * from a where exists(
8 select 1 from b where b.LCUSPLANID=a.Lcusplanid
9 )
10 )
11 ) where rn=1;LIAD CUSID LCUSPLANID TO_CHAR(LFILLDATE,'YYYY-MM-DDH
---------- -------- ---------- ------------------------------
LS0000080 C093 CL0000054 2009-09-26 17:23:36
LS0000079 C094 CL0000056 2009-09-24 18:23:36SQL>
(SELECT lid lid,cusid,lcusplanid,lfilldate FROM (SELECT 'LS0000077' LID, 'C093' CUSID, 'CL0000054' LCUSPLANID, '2009-09-25 17:23:36' lfilldate
FROM dual UNION ALL
SELECT 'LS0000080' LID, 'C093' CUSID, 'CL0000054' LCUSPLANID, '2009-09-26 17:23:36' lfilldate
FROM dual) a
UNION
SELECT lid,cusid,lcusplanid,lfilldate FROM (SELECT 'LS0000078' LID, 'C093' CUSID, 'CL0000054' LCUSPLANID, '2009-09-24 17:23:36' lfilldate
FROM dual UNION ALL
SELECT 'LS0000079' LID, 'C094' CUSID, 'CL0000066' LCUSPLANID, '2009-09-24 18:23:36' lfilldate
FROM dual) b)
select m.ft,m.cusid,m.lcusplanid,max(m.lfilldate)
from (SELECT ab.*,FIRST_VALUE(lid) OVER(PARTITION BY lcusplanid ORDER BY lfilldate DESC) ft
FROM ab) m group by ft,cusid,lcusplanid