问题与子查询
问题描述:
我有以下查询:问题与子查询
- 的packgae的状态必须为“出货:
SELECT SP.PACKAGEID,SP.ORDERID,PTS.CREATIONDATE AS SHIPPEDDATE FROM PACKAGES SP INNER JOIN ( SELECT * FROM ( SELECT *FROM PACKAGE_STATISTICS A WHERE((A.PACKAGEID=SP.PACKAGEID) AND(A.PACKAGESTATUS=2) AND(TO_DATE(to_char(A.CREATIONDATE,'mm/dd/yyyy'),'mm/dd/yyyy') BETWEEN TO_DATE('2/19/2011','mm/dd/yyyy') AND (TO_DATE('3/21/2011','mm/dd/yyyy')))) ORDER BY A.CREATIONDATE ) WHERE ROWNUM<2 ) PTS ON PTS.PACKAGEID=SP.PACKAGEID WHERE SP.ISSUEID IN(402783)
该查询基于以下条件软件包表选择包详细信息“(通过将PACKAGE_STATISTICS的PACKAGESTATUS列设置为2来表示)
- 如果包中有多个”发货“条目,请选择最新的(基于CREATIONDATE列值)。
当我运行上面的查询时,它抛出ORA-00904错误,因为级别2的子查询会刷新表名。所以,我已经修改了查询,如下所示:
SELECT SP.PACKAGEID,SP.ORDERID, (SELECT CREATIONDATE FROM
(
SELECT *FROM PACKAGE_STATISTICSA
WHERE
((A.PACKAGEID=SP.PACKAGEID)
AND(A.PACKAGESTATUS=2)
AND(TO_DATE(to_char(A.CREATIONDATE,'mm/dd/yyyy'),'mm/dd/yyyy')
BETWEEN TO_DATE('2/19/2011','mm/dd/yyyy') AND (TO_DATE('3/21/2011','mm/dd/yyyy'))))
ORDER BY A.CREATIONDATE
) WHERE ROWNUM<2) AS SHIPPEDDATE
FROM PACKAGESSP
WHERE
(SHIPPEDDATE BETWEEN TO_DATE('2/19/2011','mm/dd/yyyy')
AND (TO_DATE('3/21/2011','mm/dd/yyyy')))
AND SP.ISSUEID IN(402783)
它再次扔ORA-00904发货日期无效的。 请让我知道我该怎么做? 感谢大家, Pradeep
答
如果您只需要来自PACKAGE_STATISTICS的最新日期,为什么不简单使用MAX(CREATION_DATE)?与最新的creationdate
SELECT SP.PACKAGEID
,SP.ORDERID
,MAX(A.CREATIONDATE) AS SHIPPEDDATE
FROM PACKAGES SP
INNER JOIN PACKAGE_STATISTICS A ON A.PACKAGEID = SP.PACKAGEID
WHERE SP.ISSUEID IN (402783)
AND A.PACKAGEID = SP.PACKAGEID
AND A.PACKAGESTATUS = 2
AND TO_DATE(to_char(A.CREATIONDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') BETWEEN
TO_DATE('2/19/2011', 'mm/dd/yyyy') AND
TO_DATE('3/21/2011', 'mm/dd/yyyy')
GROUP BY SP.PACKAGEID
,SP.ORDERID
如果你想从package_statistics的行更多详细信息,请考虑使用分析功能::我现在不能测试,但你可能需要这样的查询
SELECT SP.PACKAGEID
,SP.ORDERID
,PTS.*
FROM PACKAGES SP
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY PACKAGEID ORDER BY CREATIONDATE DESC) rn
,A.*
FROM PACKAGE_STATISTICS A
WHERE A.PACKAGESTATUS = 2
AND TO_DATE(to_char(A.CREATIONDATE, 'mm/dd/yyyy')
,'mm/dd/yyyy') BETWEEN
TO_DATE('2/19/2011', 'mm/dd/yyyy') AND
TO_DATE('3/21/2011', 'mm/dd/yyyy')) PTS ON PTS.PACKAGEID =
SP.PACKAGEID
AND PTS.RN = 1;
喜Marcin,感谢您的回复......我需要每个包装的最大出货日期。即,每个包将在PACKAGE_STATISTICS表中包含零个或多个已发货状态条目。如果没有对应于包的“发货”状态条目,我不希望在结果集中包含该包。否则,我只需要最近添加的“发货”条目 – Pradeep 2011-03-22 10:35:24
因此(如果我没有制作任何语法错误)我的第一个查询应该返回你想要的。 – 2011-03-22 11:04:23
事实上,你的第二个查询确实有效。在真实场景中,我必须获取超过20个连接8个表格的列。所以,我选择了第二个。非常感谢你.. :) – Pradeep 2011-03-23 08:08:34