多表取随机记录问题

haiyuey

单表取随机记录可以用：
select * from tablename order by rand() fetch first n rows only

现有表A 内容为：
xm
张三
李四
王五
表B 内容为其他数据

现求：
表A  XM字段每一行取表B  5个随机字段
即张三，李四，王五各取表B 5个随机字段

[ 本帖最后由 haiyuey 于 2007-7-23 14:47 编辑 ]

raullew

查row_number函数用法
取前五位

haiyuey

请写出过程，谢谢！！！

haiyuey

谁能帮帮我!

haiyuey

等了好几天了

raullew

原帖由 haiyuey 于 2007-7-26 14:32 发表
等了好几天了

google

haiyuey

google 没找到

xzystream

老兄你那个是错的。
误人子弟

大梦

可以去看看SELECT的TABLESAMPLE子句或许能满足你的需要

Example 1: Request a 10% Bernoulli sample of the Sales table for auditing purposes.

SELECT * FROM Sales TABLESAMPLE BERNOULLI(10)

Example 2: Compute the total sales revenue in the Northeast region for each product category, using a random 1% SYSTEM sample of the Sales table. The semantics of SUM are for the sample itself, so to extrapolate the sales to the entire Sales table, the query must divide that SUM by the sampling rate (0.01).

SELECT SUM(Sales.Revenue) / (0.01) FROM Sales TABLESAMPLE SYSTEM(1) WHERE Sales.RegionName = ’Northeast’ GROUP BY Sales.ProductCategory

Example 3: Using the REPEATABLE clause, modify the previous query to ensure that the same (yet random) result is obtained each time the query is executed. (The value of the constant enclosed by parentheses is arbitrary.)

SELECT SUM(Sales.Revenue) / (0.01) FROM Sales TABLESAMPLE SYSTEM(1) REPEATABLE(3578231) WHERE Sales.RegionName = ’Northeast’ GROUP BY Sales.ProductCategory