oracle 把select语句中用到的时间去掉时分秒[问题点数:88分,结帖人:lzs45]
lzs45
(寻梦 MSN(Lzs45#16)
等 级:
结帖率:90.91%
楼主发表于:2006-10-15 22:43:13
代码如下:
----------------------------
SELECT NVL(T1.ol_Qty, 0),
(CASE WHEN T2.Qty = 0 THEN 0 ELSE 100 * NVL(T1.ol_Qty, 0) / T2.Qty END),
NVL(T1.ol_Num, 0),
(CASE WHEN T2.Num = 0 THEN 0 ELSE 100 * NVL(T1.ol_Num, 0) / T2.Num END)
FROM (SELECT NVL(SUM(Qty), 0) AS ol_Qty,
COUNT(DISTINCT(Customer_ID)) AS ol_Num
FROM TRD_TRADES_HIST
WHERE Trade_Date BETWEEN
To_Date( '20060718000000 ', 'yyyymmddhh24miss ') AND
To_Date( '20060918235959 ', 'yyyymmddhh24miss ')
AND ol_Flag = 'L '
AND to_date(to_char(Trade_Date, 'yyyymmdd '), 'yyyymmdd ')=Open_Trade_Date) T1,
(SELECT SUM(Qty) AS Qty,
COUNT(DISTINCT(Customer_ID)) AS Num
FROM TRD_TRADES_HIST
WHERE Trade_Date BETWEEN
To_Date( '20060718000000 ', 'yyyymmddhh24miss ') AND
To_Date( '20060918235959 ', 'yyyymmddhh24miss ')) T2
=================================================================
数据库里的Trade_Date字段带有时分秒,Open_Trade_Date字段没有时分秒,客户端提交的日期没有时分秒,有时分秒的和无时分秒的需要比较,目前知道的去掉时分秒的方法:
1、TRUNC(Trade_Date) 效率太低,尤其数据量特别大的时候。
TRUNC(Trade_Date) BETWEEN To_Date( '20060718 ', 'yyyymmdd ') AND To_Date( '20060918 ', 'yyyymmdd '))
2、To_Date(To_Char(Trade_Date)),比方法1好一点,但效率也很慢。
数据库里有几千万条数据,用上面的语句要十几分钟,哪们大哥帮小弟优化一下,谢谢了先。
对我有用[0]丢个板砖[0]引用举报管理TOP 回复次数:12
lzs45
(寻梦 MSN(Lzs45#16)
等 级:
#1楼 得分:0回复于:2006-10-15 23:01:29
晕,怎么没有夜猫子来帮我的帮呀
对我有用[0]丢个板砖[0]引用举报管理TOP
精华推荐:oracle面试题目总结---(300分相赠)!
wiler
(@_@)
等 级:
2
#2楼 得分:10回复于:2006-10-15 23:05:37
1.建立函数索引create index ix_trunc_trad on TRD_TRADES_HIST(TRUNC(Trade_Date));
2.这么大的数据量可考虑建立分区表,分区表的优点是,在磁盘提供并行读取的情况下可提高性能
对我有用[0]丢个板砖[0]引用举报管理TOP
精华推荐:特难的SQL,请高书帮助,急,跪求各位大峡!!!
Eric_1999
(Eric)
等 级:
lzs45
(寻梦 MSN(Lzs45#16)
等 级:
结帖率:90.91%
楼主发表于:2006-10-15 22:43:13
代码如下:
----------------------------
SELECT NVL(T1.ol_Qty, 0),
(CASE WHEN T2.Qty = 0 THEN 0 ELSE 100 * NVL(T1.ol_Qty, 0) / T2.Qty END),
NVL(T1.ol_Num, 0),
(CASE WHEN T2.Num = 0 THEN 0 ELSE 100 * NVL(T1.ol_Num, 0) / T2.Num END)
FROM (SELECT NVL(SUM(Qty), 0) AS ol_Qty,
COUNT(DISTINCT(Customer_ID)) AS ol_Num
FROM TRD_TRADES_HIST
WHERE Trade_Date BETWEEN
To_Date( '20060718000000 ', 'yyyymmddhh24miss ') AND
To_Date( '20060918235959 ', 'yyyymmddhh24miss ')
AND ol_Flag = 'L '
AND to_date(to_char(Trade_Date, 'yyyymmdd '), 'yyyymmdd ')=Open_Trade_Date) T1,
(SELECT SUM(Qty) AS Qty,
COUNT(DISTINCT(Customer_ID)) AS Num
FROM TRD_TRADES_HIST
WHERE Trade_Date BETWEEN
To_Date( '20060718000000 ', 'yyyymmddhh24miss ') AND
To_Date( '20060918235959 ', 'yyyymmddhh24miss ')) T2
=================================================================
数据库里的Trade_Date字段带有时分秒,Open_Trade_Date字段没有时分秒,客户端提交的日期没有时分秒,有时分秒的和无时分秒的需要比较,目前知道的去掉时分秒的方法:
1、TRUNC(Trade_Date) 效率太低,尤其数据量特别大的时候。
TRUNC(Trade_Date) BETWEEN To_Date( '20060718 ', 'yyyymmdd ') AND To_Date( '20060918 ', 'yyyymmdd '))
2、To_Date(To_Char(Trade_Date)),比方法1好一点,但效率也很慢。
数据库里有几千万条数据,用上面的语句要十几分钟,哪们大哥帮小弟优化一下,谢谢了先。
对我有用[0]丢个板砖[0]引用举报管理TOP 回复次数:12
lzs45
(寻梦 MSN(Lzs45#16)
等 级:
#1楼 得分:0回复于:2006-10-15 23:01:29
晕,怎么没有夜猫子来帮我的帮呀
对我有用[0]丢个板砖[0]引用举报管理TOP
精华推荐:oracle面试题目总结---(300分相赠)!
wiler
(@_@)
等 级:
2
#2楼 得分:10回复于:2006-10-15 23:05:37
1.建立函数索引create index ix_trunc_trad on TRD_TRADES_HIST(TRUNC(Trade_Date));
2.这么大的数据量可考虑建立分区表,分区表的优点是,在磁盘提供并行读取的情况下可提高性能
对我有用[0]丢个板砖[0]引用举报管理TOP
精华推荐:特难的SQL,请高书帮助,急,跪求各位大峡!!!
Eric_1999
(Eric)
等 级: