Original article http://www.itpub.net/showthread.php?s=&threadid=654
Oldwain
Appreciate a wonderful SQL (also come to the right, can not let them say that we will only use water). The following statement is used to randomly find three records Select * from Empno in (SELECT Empno from Emp Order by dbms_random .random) WHERE ROWNUM <= 3) /
Don't doubt what is wrong with this statement, there is no problem in Oracle815 or more.
The problem is: 1. DBMS_Random.random produces an 8-bit or 10 bit (different from the version), what is the meaning of such a big number?
2. Order By is not allowed to use a larger number than the actual number of columns (by executing the error message of the SELECT * FROM EMP ORDER BY 20)
So why can this statement be executed, how to do?
qiaoyu
I can't execute on my Oracle8.1.6!
Oldwain
Can't do it? Isn't you installed dbms_random package? There should be no problem after installation.
Execute Oracle_Home / Rdbms / Admin / CatOctk.sql, install dbms_random package
If it is 8.1.5, it is also necessary before executing the above statement, executes: execute dbms_random.initialize (1000); - 1000 is a seed that generates a random number, and can take any values.
WINDFF
Very fun SQL, dbms_random.random generated a series of numbers, not what is the number, it is easy to understand as a column, if SQL writes this, it is easy to understand Select * from Empno in (SELECT Empno, dbms_random. Random A Oder By a) WHERE ROWNUM <3) Oder by is to follow a constant, this constant is treated as a column, for example: select * from Emp ORDER BY TO_NUMBER ('10 ') is this? Oldwain
It makes sense!
I turned out that the corner of the corner, I always thought that the number behind the ORDER BY must be a column number.
Thank you for the explanation of WINDFF, this problem made me wrapped up for a long time.
WINDFF
This problem is very fun, such SQL is not easy to see, but also thank you.
Oldwain
I remember that I was 92, 93, I saw a book about OCCC (Obfuscated C code Contest, the original) book, very interesting, I don't know if there is any Obfuscated SQL Code Contest?
The following is a code of the first OCCC (for later future, it is very simple), interested in C, may wish to explain:
Code: ------------------------------------------------ ------------------------------- INT I; main () {for (; i ["] The following program can be the best program, compatible with C, Fortran77, Bourne Shell !!!!!!! Code: ------------------- -------------------------------------------------- ----------- CAT = 13 / * /> / dev / null 2> & 1; echo "Hello, World!"; EXIT ** This Program Works under CC, F77, And /bin/sh.**/; main () {Write CAT- ~ -CAT / *, '(* /, "Hello, World!", CAT); PUTCHAR (~ - ~ ~ -cat);} / *,)') end * / -------------------------------------------------- ------------------------------ WINDFF Oldwin has a good thing, I have seen the above program heads. It has always thought that I have a lot of knowing myself. Now I will see it, I don't know anything, watch Yang Zhen wants to study hard. Oldwain Oh, this has nothing to do with the individual's level. If it is easy to understand, it cannot be called "obfuscated". The following is the introductory of Obfuscated C, requiring its functionality in the fastest time (such as 10 seconds): Code: ------------------------------------------------ ------------------------------- {a = a b; b = a - b; a = a -b } --------------------------------------------------------------------------------------------- -------------------------------- WINDFF It's really confused code. It is actually, A and B have called, it's really admiring, in order to define a variable, actually use this way, admire, this book is, I really want to find it. Young107 Interesting SQL, complex C statement, winding my head is dizzy. However, the function of the last section seems to be assiable 0 to 0. Oldwain Windff is right, this method is indeed higher than using a temporary variable!!! Want to see more OCCC, you can go to http://www.ioccc.org/ If you are interested, you can also go to the competition (I don't know if the bonus situation) WINDFF Thank you, Oldwain, this problem is interesting, you can experience fun, not like now, most of the time is moving, there is no fun. Feng_XIN Do not understand Select * from Emp Where Empno in (Select Empno from Emp Order by DBMS_Random.random) Where rownum <= 3) 1. DBMS_Random.random returns a random number, then the order by dbms_random.random should be equivalent to the order by '20'? 2. If select Empno from Emp ORDER BY '20', does the Order By can't work? 3. So this SQL is not always returned to the first three records? Is it that this should be written? Select * from Emp Where Empno in (SELECT Empno from (SELECT Empno, DBMS_RAndom.random AAFROM EMP ORDER BY AA) Where Rownum <= 3) Oldwain The two statements should be equivalent. Note: 1. If select Empno from Emp ORDER BY 20, it will be wrong (20 is not a valid list number), but select Empno from Emp ORDER BY '20' No problem (to "20 'As a constant calculation column) 2. Order by dbms_random.random should be equivalent to order by '20'? No! Dbms_random.random is not only calculated once, but calculates one for each row, so get a variable computing column, not a constant calculation column . Do a test, you can understand the difference between this. Internal This C pro program tastes, and many students have a lot of students in the university. The program can also be compiled like the first poem. You stand on the bridge looking at the scenery, looking at the scenery looks at you upstairs. The moon decorated your window, you decorated others' dreams.之 Parrotao Add this Topic To Our E-MAGAZINE? IT is Very Very Very Funny, Very Very Very Exciting !!! Thanks All Oldwain Oh, put this post and turn it out? I also hope to do something similar to the like to put into E-MAG. Unfortunately, there is no resource in this area. Does anyone provide some tassels that have fun SQL (similar to the OCCC like I mentioned above?
