SELECT A.A1, A.A2, B.B1, C.C1, C.C2
FROM (SELECT MIN(RCVDATE) AS A1
,MIN(RCVTIME) AS A2
FROM TAPPROVALINFO
WHERE RCVDATE = (SELECT MIN(RCVDATE) FROM TAPPROVALINFO)) A
INNER JOIN (SELECT COUNT(*) AS B1 FROM TAPPROVALINFO) B ON(0 = 0)
INNER JOIN (SELECT MAX(RCVDATE) AS C1
,MAX(RCVTIME) AS C2
FROM TAPPROVALINFO
WHERE RCVDATE = (
SELECT MAX(RCVDATE)
FROM TAPPROVALINFO)) C ON(0 = 0)看起来很累,能改成看容易点吗?
FROM (SELECT MIN(RCVDATE) AS A1
,MIN(RCVTIME) AS A2
FROM TAPPROVALINFO
WHERE RCVDATE = (SELECT MIN(RCVDATE) FROM TAPPROVALINFO)) A
INNER JOIN (SELECT COUNT(*) AS B1 FROM TAPPROVALINFO) B ON(0 = 0)
INNER JOIN (SELECT MAX(RCVDATE) AS C1
,MAX(RCVTIME) AS C2
FROM TAPPROVALINFO
WHERE RCVDATE = (
SELECT MAX(RCVDATE)
FROM TAPPROVALINFO)) C ON(0 = 0)看起来很累,能改成看容易点吗?
最小时刻(RCVTIME):在包括最小日期的几条数据中最小时刻相反最大时刻也同样。在包括最大日期的几条数据中最大时刻比如,有下列数据
下列数据:
日期 时刻
20080101 120101
20080101 050101
20080101 030101
20100101 090101
20100101 050101
20100101 010101我要的结果是:
最小日期 最大日期 最小时刻 最大时刻 总件数
20080101 20100101 030101 090101 6
请指教。
?? on (true)
?
--临时表作成
with a as(
select '20080101' as r, '120101' as s from dual
union all select '20080101', '050101' from dual
union all select '20080101', '030101' from dual
union all select '20100101', '090101' from dual
union all select '20100101', '050101' from dual
union all select '20100101', '010101' from dual
)
--正文
select max(R) KEEP(DENSE_RANK FIRST ORDER BY R) AS ZHUXIAOR
,max(R) KEEP(DENSE_RANK FIRST ORDER BY R DESC) AS ZHUDAR
,max(S) KEEP(DENSE_RANK FIRST ORDER BY R,S) AS ZHUXIAOS
,max(S) KEEP(DENSE_RANK FIRST ORDER BY R DESC,S DESC) AS ZHUDAS
,COUNT(1) AS JIANSHU
from a
结果ZHUXIAOR ZHUDAR ZHUXIAOS ZHUDAS JIANSHU
20080101 20100101 030101 090101 6
,max(RCVDATE) KEEP(DENSE_RANK FIRST ORDER BY RCVDATE DESC) AS ZHUDAR
,max(RCVTIME) KEEP(DENSE_RANK FIRST ORDER BY RCVDATE,RCVTIME) AS ZHUXIAOS
,max(RCVTIME) KEEP(DENSE_RANK FIRST ORDER BY RCVDATE DESC,RCVTIME DESC) AS ZHUDAS
,COUNT(1) AS JIANSHU
from TAPPROVALINFO
你的方法太强了,以前没用过keep谢谢了。。结贴了