假设有一张表phonenoheadtbl,其表结构如下:
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| Id | bigint(20) | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | NO | | NULL | |
| No | varchar(7) | NO | | NULL | |
| Type | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
现在要从大量的表数据中随机获取一条数据,查询语句如下:
SELECT
*
FROM
phonenoheadtbl AS t1
JOIN (
SELECT
ROUND(
RAND() * (
(
SELECT
MAX(Id)
FROM
phonenoheadtbl
) - (
SELECT
MIN(Id)
FROM
phonenoheadtbl
)
) + (
SELECT
MIN(Id)
FROM
phonenoheadtbl
)
) AS Id
) AS t2
WHERE
t1.Id >= t2.Id
ORDER BY
t1.Id
LIMIT 1