表1有个时间列 |表2有个时间列
2008-01-01 44:32 |2008-01-01 51:21
用什么办法把它写成可以作为条件相等的写法?
就是 WHERE 表1.TO_DATE = 表2.TO_DATEselect to_char(t1.t_date, 'yyyy-mm') t_date,
t2.name,
t1.item_name,
t1.spec,sum(to_number(substr(t1.amount, 1, length(t1.amount) - 2))) amount,t1.units
from table1 t1, table2 t2
where t1.t_date[2008-01-01 44:32] = t2.sss_date[2008-01-01 55:11]/*就这个意思*/
and t1.id = t2.id
and to_char(t1.t_date, 'yyyy-mm-dd') >= '2008-07-01'
and to_char(t1.t_date, 'yyyy-mm-dd') <= '2008-07-31'
group by to_char(t1.t_date, 'yyyy-mm'),
t2.name,
t1.item_name,
t1.spec,
t1.units;
请高手们帮整看~谢为先~
2008-01-01 44:32 |2008-01-01 51:21
用什么办法把它写成可以作为条件相等的写法?
就是 WHERE 表1.TO_DATE = 表2.TO_DATEselect to_char(t1.t_date, 'yyyy-mm') t_date,
t2.name,
t1.item_name,
t1.spec,sum(to_number(substr(t1.amount, 1, length(t1.amount) - 2))) amount,t1.units
from table1 t1, table2 t2
where t1.t_date[2008-01-01 44:32] = t2.sss_date[2008-01-01 55:11]/*就这个意思*/
and t1.id = t2.id
and to_char(t1.t_date, 'yyyy-mm-dd') >= '2008-07-01'
and to_char(t1.t_date, 'yyyy-mm-dd') <= '2008-07-31'
group by to_char(t1.t_date, 'yyyy-mm'),
t2.name,
t1.item_name,
t1.spec,
t1.units;
请高手们帮整看~谢为先~
SELECT TO_CHAR(T1.T_DATE, 'yyyy-mm') T_DATE,
T2.NAME,
T1.ITEM_NAME,
T1.SPEC,
SUM(TO_NUMBER(SUBSTR(T1.AMOUNT, 1, LENGTH(T1.AMOUNT) - 2))) AMOUNT,
T1.UNITS
FROM TABLE1 T1, TABLE2 T2
WHERE TRUNC(T1.T_DATE) = TRUNC(T2.SSS_DATE)
AND T1.ID = T2.ID
AND T1.T_DATE >= to_date('2008-07-01', 'yyyy-mm-dd')
AND T1.T_DATE <= to_date('2008-07-31', 'yyyy-mm-dd')
GROUP BY TO_CHAR(T1.T_DATE, 'yyyy-mm'),
T2.NAME,
T1.ITEM_NAME,
T1.SPEC,
T1.UNITS;
改下时间最大值,否则可能取数有问题。
SELECT TO_CHAR(T1.T_DATE, 'yyyy-mm') T_DATE,
T2.NAME,
T1.ITEM_NAME,
T1.SPEC,
SUM(TO_NUMBER(SUBSTR(T1.AMOUNT, 1, LENGTH(T1.AMOUNT) - 2))) AMOUNT,
T1.UNITS
FROM TABLE1 T1, TABLE2 T2
WHERE TRUNC(T1.T_DATE) = TRUNC(T2.SSS_DATE)
AND T1.ID = T2.ID
AND T1.T_DATE >= to_date('2008-07-01', 'yyyy-mm-dd')
AND T1.T_DATE < to_date('2008-07-31', 'yyyy-mm-dd')+1
GROUP BY TO_CHAR(T1.T_DATE, 'yyyy-mm'),
T2.NAME,
T1.ITEM_NAME,
T1.SPEC,
T1.UNITS;
我改写成了如下写法,经测试可用:
select to_char(t1.t_date, 'yyyy-mm') t_date,
t2.name,
t1.item_name,
t1.spec,sum(to_number(substr(t1.amount, 1, length(t1.amount) - 2))) amount,t1.units
from table1 t1, table2 t2
where to_char(t1.t_date, 'yyyy-mm-dd') = to_char(t2.sss_date, 'yyyy-mm-dd')
and t1.id = t2.id
and to_char(t1.t_date, 'yyyy-mm-dd') >= '2008-07-01'
and to_char(t1.t_date, 'yyyy-mm-dd') <= '2008-07-31'
group by to_char(t1.t_date, 'yyyy-mm'),
t2.name,
t1.item_name,
t1.spec,
t1.units;
and to_char(t1.t_date, 'yyyy-mm-dd') <= '2008-07-31'不建议这样的写法
1、假设t_date上索引(一般都会在常用的查询上,如日期上建成索引)
用to_char会使索引失效,就变成了全表扫描
2、字符型号的效率要比DATE型的效率差点
用什么办法把它写成可以作为条件相等的写法,所以举了上述例子.
select to_char(a1.t_date,'yyyy-mm') t_date,
a2.name,
a1.item_name,
a1.spec, //原:a.spec
to_char(sum(to_number(substr(amount,1,length(amount-1))))) ¦ ¦'只' amount,units
//原: to_char(sum(to_number(substr(amount,1,length(amount-1)))) ¦ ¦'只' amount,units
from a1,a2
where a1.T_DATE=a2.T_DATE
and a1.ID=a2.ID
and a1.t_date between to_date('2008-7-1','yyyy-mm-dd')
and to_date('2008-7-31','yyyy-mm-dd')+0.99999
and a1.item_name = '灯泡' ; group by to_char(a1.t_date,'yyyy-mm'),
a2.name,
a1.item_name,
a1.spec, //原:a.spec
units
where to_char(t1.t_date, 'yyyy-mm-dd') = to_char(t2.sss_date, 'yyyy-mm-dd')
这样的目的,用DATE型该怎样写?