Java开发网 - 请教一个有关JDBC的问题 (ORDER BY RAND())

Topic: 请教一个有关JDBC的问题 (ORDER BY RAND())

1.请教一个有关JDBC的问题 (ORDER BY RAND())

Posted by: pandagao
Posted on: 2005-04-04 13:28

select `ID`,`MSISDN` FROM `MSISDN`.`check_usr_new` where `AREA_CODE`='0571' and flag=0 limit "+ limit +" ORDER BY RAND ( )";
这样的语句在PHP上可以运行.为什么用JAVA就报错?真是不明白.那位高手能指教一下吗?
出错信息为:SQL error.java.sql.SQLException: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY RAND ( )' at line 1

2.Re:请教一个有关JDBC的问题 [Re: pandagao]

Copy to clipboard

Posted by: 九佰
Posted on: 2005-04-04 17:05

select * FROM `MSISDN`.`check_usr_new`;

select `ID`,`MSISDN` FROM `MSISDN`.`check_usr_new`;

select `ID`,`MSISDN` FROM `MSISDN`.`check_usr_new` where `AREA_CODE`='0571' and flag=0 ";

select `ID`,`MSISDN` FROM `MSISDN`.`check_usr_new` where `AREA_CODE`='0571' and flag=0 limit "+ limit ;

select `ID`,`MSISDN` FROM `MSISDN`.`check_usr_new` where `AREA_CODE`='0571' and flag=0 limit "+ limit +" ORDER BY RAND ( )";

按照以上顺序依次测试。

3.Re:请教一个有关JDBC的问题 [Re: 九佰]	Copy to clipboard
Posted by: pandagao Posted on: 2005-04-04 21:08 我前面的都测试过了.就是加上了rand()就不行了.这是为什么呀?

4.Re:请教一个有关JDBC的问题 [Re: pandagao]	Copy to clipboard
Posted by: why Posted on: 2005-04-04 22:00 Which version of MySQL and JDBC driver are you using? Are you sure the syntax is correct? i.e. LIMIT n ORDER BY RAND() or ORDER BY RAND() LIMIT n

5.Re:请教一个有关JDBC的问题 [Re: pandagao]

Copy to clipboard

Posted by: 九佰
Posted on: 2005-04-05 11:31

RAND()
RAND(N)
Returns a random floating-point value in the range from 0 to 1.0. If an integer argument N is specified, it is used as the seed value (producing a repeatable sequence).
mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. As of MySQL 3.23, you can retrieve rows in random order like this:
mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows:
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
-> ORDER BY RAND() LIMIT 1000;

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed. RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version.