need help --- trunc(x,y)

显示全部楼层 · 2002-4-13 00:00:00

Could anyone help?
How to understand the following:
trunc(x,y)--truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point. This can also be use don DATE columns.
What is the different between trunc(x,y) and round(x,y)?

千问 · 2002-4-13 00:00:00

trunc(15.683, 2) => 15.68round(15.683, 2) => 15.68
trunc(15.683, 1) => 15.6round(15.683, 1) => 15.7
trunc(15.683, 0) => 15 round(15.683, 0) => 16
trunc(15.683, -1)=> 10 round(12.683, -1) => 20
see the dirfference? trunc just cuts off while round rounds it up.

千问 · 2002-4-13 00:00:00

Thank you, georgio. Now, it is clear.
Could you give an example of trunc date?

千问 · 2002-4-13 00:00:00

trunc(16-Dec-02) => 01-Dec-02
trunc(12-Dec-02) => 01-Dec-02
Not quite sure of the date format here though. You can check it up in the bibles.

千问 · 2002-4-13 00:00:00

Thanks for your idea. I tried, and the format like the following.
SQL> select trunc(to_date('16-DEC-02','dd-mon-yyyy')) from dual;
TRUNC(TO_DATE('16-DE
--------------------
16-DEC-0002 00:00:00
SQL> select trunc(to_date('16-DEC-02','dd-mon-yyyy'), 'yyyy') from dual;
TRUNC(TO_DATE('16-DE
--------------------
01-JAN-0002 00:00:00
SQL> select trunc(to_date('16-DEC-02','dd-mon-yyyy'), 'mon') from dual;
TRUNC(TO_DATE('16-DE
--------------------
01-DEC-0002 00:00:00
SQL> select trunc(to_date('16-DEC-02','dd-mon-yyyy'), 'dd') from dual;
TRUNC(TO_DATE('16-DE
--------------------
16-DEC-0002 00:00:00
Another question:
If a table contains a large number of records, use which one will be executed faster,where ... in ,where exists,and why?

千问 · 2002-4-13 00:00:00

I didn't pay attention to the details. Sorry for my mistakes here, if any.
What do you mean by saying "where exists", I don't quite understand despite the fact that I'd like to say this one is faster than "where ... in ()".

千问 · 2002-4-13 00:00:00

These are just examples of 'where..in' and 'where exists', sometimes they can be used to get same results.
--> select ename,job, sal from emp//where col_nm in
where deptno in (select deptno from dept where dname in
('ACCOUNTING','SALE', .....));

--> select e.ename, e.job, e.sal from emp e//where exists
where exists(select d.deptno from dept d

where d.loc = 'NEW YORK' and d.deptno = e.deptno);
-----------------------------------------
What I am asking is if I deal with same tables with many many records and want to get same results, which one will be executed faster, 'where ... in' , or 'where exists', and why?This is an interview question. I think the key is the table contains many many records. This makes the two SQL different.
What I understand the difference is 'where...in' has to compare each value inside (), and 'where exists' is only test true or false inside (). I choose 'where exists'. But I want to find the right answer and the reason.