有商品表 T1MM11_GOODS_CODE
主key是商品code(GOODS_CODE),有效日(ORDER_EFFECT_DATE)
要抽出这个表基于某个日期(下面的例子用2008/12/19)的所有有效商品code
我这里有2中做法
A方法
select T1.GOODS_CODE
from T1MM11_GOODS_CODE T1
inner join
(select GOODS_CODE,Max(ORDER_EFFECT_DATE) as ORDER_EFFECT_DATE
from T1MM11_GOODS_CODE T2
Where ORDER_EFFECT_DATE<'2008/12/19'
group by GOODS_CODE) T3
On T1.GOODS_CODE=T3.GOODS_CODE
And T1.ORDER_EFFECT_DATE=T3.ORDER_EFFECT_DATE
Where T1.DEL_FLG='0'B方法
select T1.GOODS_CODE
from T1MM11_GOODS_CODE T1
where ORDER_EFFECT_DATE=
(select Max(ORDER_EFFECT_DATE) as ORDER_EFFECT_DATE
from T1MM11_GOODS_CODE T2
Where ORDER_EFFECT_DATE<'2008/12/19'
AND T1.GOODS_CODE=T2.GOODS_CODE
group by GOODS_CODE)
and T1.DEL_FLG='0'现在表的数据是14万
执行的结果是B方法明显比A方法快
但是换其他的表后,有A方法比B方法快的情况
请高人指点,是不是在某些特定情况下,A,B方法各有快慢?
或者那位有更好的做法?
谢谢指点
主key是商品code(GOODS_CODE),有效日(ORDER_EFFECT_DATE)
要抽出这个表基于某个日期(下面的例子用2008/12/19)的所有有效商品code
我这里有2中做法
A方法
select T1.GOODS_CODE
from T1MM11_GOODS_CODE T1
inner join
(select GOODS_CODE,Max(ORDER_EFFECT_DATE) as ORDER_EFFECT_DATE
from T1MM11_GOODS_CODE T2
Where ORDER_EFFECT_DATE<'2008/12/19'
group by GOODS_CODE) T3
On T1.GOODS_CODE=T3.GOODS_CODE
And T1.ORDER_EFFECT_DATE=T3.ORDER_EFFECT_DATE
Where T1.DEL_FLG='0'B方法
select T1.GOODS_CODE
from T1MM11_GOODS_CODE T1
where ORDER_EFFECT_DATE=
(select Max(ORDER_EFFECT_DATE) as ORDER_EFFECT_DATE
from T1MM11_GOODS_CODE T2
Where ORDER_EFFECT_DATE<'2008/12/19'
AND T1.GOODS_CODE=T2.GOODS_CODE
group by GOODS_CODE)
and T1.DEL_FLG='0'现在表的数据是14万
执行的结果是B方法明显比A方法快
但是换其他的表后,有A方法比B方法快的情况
请高人指点,是不是在某些特定情况下,A,B方法各有快慢?
或者那位有更好的做法?
谢谢指点
解决方案 »
- 一个关于pl\sql的小问题,急!!!!!!
- 关于PL-SQL工具请教两个问题
- 怎么在pl/sql中测试存储过程
- oracle里包内的函数能返回一个table型吗?比如我要此函数返回的是一组ID,而且在别的函数中能调用
- 一个关于序列参数的问题
- Oracle数据库中日期存的是字符性,值为13位的数据串,如"1136836800000",请问哪位大虾知道有什么函数可以直接转换,谢谢!!
- 急问!!!!!!!!!!!!!!!!!!!!!SQL语句
- 请问,如何让序列对象一次增长大于1的数
- 透明网关技术--Sybase篇(中文)
- 有个问题一直想不通,请问有人知道ASP能否调用oracle大型字段BLOB,CLOB............
- 关于临时表,临时表变量使用的时候,insert数据,这些数据是在内存中,还是数据文件中,是否会100%成功呢?
- 帮新人推荐本书?
from
(select *
from T1MM11_GOODS_CODE
where ORDER_EFFECT_DATE <'2008/12/19'
order by ORDER_EFFECT_DATE desc)
where rownum=1
from (select GOODS_CODE,
row_number() over(partition by GOODS_CODE order by ORDER_EFFECT_DATE desc from T1MM11_GOODS_CODE) rn
Where ORDER_EFFECT_DATE < '2008/12/19'
and T1.DEL_FLG = '0')
where rn = 1
from (select GOODS_CODE,
row_number() over(partition by GOODS_CODE order by ORDER_EFFECT_DATE desc) rn
from T1MM11_GOODS_CODE
Where ORDER_EFFECT_DATE < '2008/12/19'
and T1.DEL_FLG = '0')
where rn = 1
select GOODS_CODE
from (select GOODS_CODE,
row_number() over(partition by GOODS_CODE order by ORDER_EFFECT_DATE desc) rn
from T1MM11_GOODS_CODE
Where ORDER_EFFECT_DATE < '2008/12/19'
and DEL_FLG = '0')
where rn = 1
select *
from T1MM11_GOODS_CODE a
where not exists(select 1 from T1MM11_GOODS_CODE where ORDER_EFFECT_DATE=a.ORDER_EFFECT_DATE and ORDER_EFFECT_DATE>a.ORDER_EFFECT_DATE)參照寫法;有幾种方法都是通用的
http://topic.csdn.net/u/20081106/11/b2f6633d-f96e-444f-9264-8d8528d308fa.html
這個
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
我之前也是试过,速度是介于A,B之间
我想了解的是,什么是可能影响A,B执行难效率的因素
那为什么要这么麻烦呢
select distinct GOODS_CODE
from T1MM11_GOODS_CODE
where ORDER_EFFECT_DATE <'2008/12/19'
and DEL_FLG = '0'
这样不就好了吗?
from T1MM11_GOODS_CODE T1
where ORDER_EFFECT_DATE=
(select Max(ORDER_EFFECT_DATE) as ORDER_EFFECT_DATE
from T1MM11_GOODS_CODE T2
Where ORDER_EFFECT_DATE <'2008/12/19'
AND T1.GOODS_CODE=T2.GOODS_CODE
--group by GOODS_CODE 这个group by 加在这干嘛呢,多余
)
and T1.DEL_FLG='0'
SELECT T1.GOODS_CODE
FROM T1MM11_GOODS_CODE T1,
(SELECT GOODS_CODE, MAX(ORDER_EFFECT_DATE) AS ORDER_EFFECT_DATE
FROM T1MM11_GOODS_CODE
WHERE ORDER_EFFECT_DATE < '2008/12/19'
AND DEL_FLG = '0'
GROUP BY GOODS_CODE) T2
WHERE T1.GOODS_CODE = T2.GOODS_CODE
AND T1.ORDER_EFFECT_DATE < '2008/12/19'
AND T1.DEL_FLG = '0'
SELECT DISTINCT GOODS_CODE
FROM T1MM11_GOODS_CODE
WHERE ORDER_EFFECT_DATE < '2008/12/19'
AND DEL_FLG = '0';