sql - 使用 NEWID() 从表中检索随机行

Question

我有一个 sql 查询，我正在使用 distinct 语句

   CREATE proc SProc_SelectPhotographersByLocation         
@locationID varchar(500)          
as            
begin          


DECLARE @TEST varchar(1000)          
DECLARE @SQLQuery AS NVARCHAR(1000)          

SET @TEST = @locationID          

SET @SQLQuery = 'select distinct ContributerSubCategoryMapping.ContributorID,  PhotographerContributors_tbl.*  from ContributerSubCategoryMapping               
  left outer join PhotographerContributors_tbl on PhotographerContributors_tbl.ContributorId=ContributerSubCategoryMapping.ContributorID              
  left outer join tbl_City on tbl_City.CityID=PhotographerContributors_tbl.Location              
  where         
  PhotographerContributors_tbl.Location IN('+ @locationID +') and PhotographerContributors_tbl.IsActive=''1'' order by Newid()'          
EXECUTE(@SQLQuery)          

end

当我在该查询上使用 NEWID() 时，我在查询中遇到错误。错误是

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

请帮我解决这个问题

Answer 1

使用 group by 而不是 distinct。一种方法是明确列出列:

select csm.ContributorID, pc.col1, pc.col2, . . .
from ContributerSubCategoryMapping csm left outer join
     PhotographerContributors_tbl pc
     on pc.ContributorId = csm.ContributorID left outer join
     tbl_City c
     on c.CityID = pc.Location              
where pc.Location IN ('+ @locationID +') and pc.IsActive=''1''
group by csm.ConstributorId, pc.col1, pc.col2, . . .
order by Newid();

但是，我不明白查询。表 ContributerSubCategoryMapping 和 tbl_City 似乎没有被使用。那么，为什么不直接这样做呢？

select pc.*
from PhotographerContributors_tbl pc
where pc.Location IN ('+ @locationID +') and pc.IsActive=''1''
order by Newid();