如何从SQL Server中的表中选择随机行 [英] How to select random row from table in sql server

问题描述

要从表中选择随机行，我必须像这样返回

To select random row from table I have return like this

ALTER PROCEDURE [dbo].[SP_GetPersonHistory]
(
@CompanyName varchar(50),
@CompanyUrl varchar(50)
)
AS
BEGIN
SELECT t.CompanyName,t.Country,t.PersonName,t.Person_EmailId,t.ProductNames,t.CompanyType,t.State,t.ZipCode,t.Person_Title,t.PhoneNumber
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.CompanyId ORDER BY NEWID()) AS Seq,*
FROM Companies c inner join Persons p on p.CompanyId=c.CompanyId where c.CompanyName=@CompanyName and c.CompanyUrl=@CompanyUrl order by p.PersonID desc
) as t
WHERE Seq<=1

END

但这显示了错误:
列"companyid"多次指定了t.
和
除非还指定了TOP或FOR XML，否则ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效.

But it is showing error :
the column "companyid" specified multiple times for t.
and
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

推荐答案

错误消息像往常一样是真实的.

...列"companyid"多次指定用于...
它一次来自c，一次来自p.
通常，选择所有列(''*'')是一个坏主意.

The error messages, as usual, are true.

...column "companyid" specified multiple times for t...
It comes once from c, once from p.
Generally, selecting all columns (''*'') is a bad idea.

ALTER PROCEDURE [dbo].[SP_GetPersonHistory]
(
@CompanyName varchar(50),
@CompanyUrl varchar(50)
)
AS
BEGIN
SELECT t.CompanyName,t.Country,t.PersonName,t.Person_EmailId,t.ProductNames,t.CompanyType,t.State,t.ZipCode,t.Person_Title,t.PhoneNumber
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.CompanyId ORDER BY NEWID()) AS Seq,*
FROM Companies c inner join Persons p on p.CompanyId=c.CompanyId where c.CompanyName=@CompanyName and c.CompanyUrl=@CompanyUrl order by p.PersonID desc
) as t
WHERE Seq<=1
 
END

ORDER BY子句在(...)子查询(...)
中无效 您已经在子查询t
中找到了它

The ORDER BY clause is invalid in (...) subqueries (...)
You have it in the subquery t

ALTER PROCEDURE [dbo].[SP_GetPersonHistory]
(
@CompanyName varchar(50),
@CompanyUrl varchar(50)
)
AS
BEGIN
SELECT t.CompanyName,t.Country,t.PersonName,t.Person_EmailId,t.ProductNames,t.CompanyType,t.State,t.ZipCode,t.Person_Title,t.PhoneNumber
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.CompanyId ORDER BY NEWID()) AS Seq,*
FROM Companies c inner join Persons p on p.CompanyId=c.CompanyId where c.CompanyName=@CompanyName and c.CompanyUrl=@CompanyUrl order by p.PersonID desc
) as t
WHERE Seq<=1
 
END

check this link ...it may help you .. . . . . like it helped me . . .

<a href="http://webgeek.elletis.com/how-to-choose-random-record-from-sql-server-database/">choose-random-record-from-sql-server-database/</a>[<a href="http://webgeek.elletis.com/how-to-choose-random-record-from-sql-server-database/" target="_blank" title="New Window">^</a>]

这篇关于如何从SQL Server中的表中选择随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！