有商品表 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方法各有快慢?
或者那位有更好的做法?
谢谢指点
解决方案 »
- 求救..ORA-12154: TNS:could not resolve the connect identifier specified~~搞了好久了没解决!
- 求账龄分析表SQL
- 在oracle中数据库实例不可用并且到实例的代理连接失败 请问怎么处理?
- 把sqlserver 存储过程转换为oracle存储过程
- oracle初学者问个基本问题,请高手帮忙!本人感激不尽!
- 这样的SQL该怎么写?
- 小问题
- 超奇怪的问题
- 怎样验证sql语句的正确性(只是语法检查,不执行)
- 安装ORACLE CELINE 10g 总是停止在52%,无法完成安装
- 关于临时表,临时表变量使用的时候,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';