MS SQL Server选择没有重复项的随机行

I need to select a random amount of rows but also ensure that the rows don't contain duplicate Image values:

我需要选择随机数量的行,但也要确保行不包含重复的Image值:

ImgID   Image

1637    PM1371564839.jpg    
1638    PM1371564839.jpg    
1639    PM1371564840.jpg    
1640    PM1371564840.jpg    
1641    PM1371564840.jpg    
1642    PM1371564841.jpg    
1643    PM1371564842.jpg    
1644    PM1371564842.jpg    
1645    PM1371564842.jpg    
1646    PM1371564843.jpg    
1647    PM1371564843.jpg

I have done a - select top 25 percent * from Table order by newid(); This works for the random side of things but it brings back duplicate. I have tried a distinct but it doesnt like the order by part. Basically is there a better way to show just show a random 3 Images that are not duplicates.

我做了一个 - 从表顺序中选择前25%* by newid();这适用于事物的随机方面,但它会带来重复。我尝试了一个独特的但它不喜欢订单的顺序。基本上有一个更好的方式来显示只是显示一个随机的3图像不重复。

4 个解决方案

#1

looks like you have multiple images with different ImgID

看起来你有多个具有不同ImgID的图像

maybe you want something like this to get unique images

也许你想要这样的东西来获得独特的图像

SELECT TOP 25 PERCENT * FROM 
( 
  SELECT 
    max(imgID) imgID,  
    Image
  FROM [table]
  GROUP BY [Image]
) x
ORDER BY newid();

#2

I would start with something like:

我会从以下内容开始:

SELECT TOP 25 PERCENT
 *
FROM
  (SELECT DISTINCT image FROM table) -- only unique image values
ORDER BY
 newid() -- random sort

Keep in mind that his query has a few issues:

请记住,他的查询有一些问题:

Subselect reads the whole table and makes DISTINCT on it (probably need index).

Subselect读取整个表并对其进行DISTINCT(可能需要索引)。

It's hard to JOIN other columns from TABLE table (no PK which would destroy DISTINCT) in outer SELECT.

很难在TABLE表中加入TABLE表中的其他列(没有PK会破坏DISTINCT)。

Here is more readable version of above query:

以下是上述查询的更易读的版本:

;WITH unique_images AS (
  SELECT DISTINCT image FROM TABLE
)
SELECT TOP 25 PERCENT
  *
FROM
  unique_images
ORDER BY
  new_id()

#3

Use Northwind
GO


select top 10 CustomerID , OrderID
from
(
select distinct CustomerID, OrderID from dbo.[Orders]
) as derived1

order by newid()

Mapped to yours:

映射到你的:

select top 25 PERCENT ImgID , Image
from
(
select distinct ImgID , Image from dbo.[MyTable]
) as derived1

order by newid()

#4

This would do it:

这样做:

SELECT TOP 3 ImgID, Image
FROM (SELECT DISTINCT ImgID, Image
      FROM #MyTABLE
      )sub
ORDER BY NEWID()

Demo: SQL Fiddle

演示:SQL小提琴

#1