在 Sql Server 中的随机连接

Question

我有两个表，一个是Product，一个是ProductImage。

这两个表之间存在1-n的关系。每个产品都对应多个图片，具体数量取决于产品本身。

我想创建一个产品视图，并且想从ProductImage表中为每个产品随机获取一张图片。

数据示例：http://sqlfiddle.com/#!6/43c69

我想要的结果如下所示。

+-----------+------+-------------+
| ProductId | Name |   WebPath   |
+-----------+------+-------------+
|         1 | Foo  | foowebpath2 |
|         2 | Boo  | boowebpath3 |
|         3 | Zoo  | zoowebpath1 |
+-----------+------+-------------+

或者

+-----------+------+-------------+
| ProductId | Name |   WebPath   |
+-----------+------+-------------+
|         1 | Foo  | foowebpath1 |
|         2 | Boo  | boowebpath1 |
|         3 | Zoo  | zoowebpath6 |
+-----------+------+-------------+

或者

+-----------+------+-------------+
| ProductId | Name |   WebPath   |
+-----------+------+-------------+
|         1 | Foo  | foowebpath4 |
|         2 | Boo  | boowebpath2 |
|         3 | Zoo  | zoowebpath5 |
+-----------+------+-------------+

或等等...

它每次都必须不同。

- Sinan AKYAZICI

Answer 1

你应该尝试这个

SELECT *, (SELECT TOP 1 WebPath FROM ProductImage PI 
WHERE PI.ProductId = P.ProductId order by NEWID()  ) as WebPart from Product P

请查看此代码片段 http://sqlfiddle.com/#!6/43c69/16

- Yograj Gupta

Answer 2

WITH Image AS
(
  SELECT *, RAND(ProductImageId) R
  FROM ProductImage 
)

SELECT p.*, i2.* FROM Product P
INNER JOIN 
    (SELECT ProductId, MIN(R) R
     FROM Image
     GROUP BY ProductId) i1 ON i1.ProductId = p.ProductId
INNER JOIN Image i2 ON i2.R = i1.R

- T I