SQL查询如何从另一个表中获取随机记录到每个行的另一个表上的查询？

发布于 2025-02-03 06:41:36 字数 537 浏览 3 评论 0原文

我试图从表中生成一个随机值，以将其编写在另一个表上的查询中。数据库中有两个表（数据库名称“ TestDatabase”）： a）[dbo]。[firstName_temp] - 包含一个名称的列表（一个称为“ firstName”的列） b）[dbo]。[lastName_temp] - 包含姓氏（一个称为“ lastName”的列）的列表（

我使用lastName_Temp表作为基础，以在查询中为每个姓氏带来随机的名字。与具有约5K的名称表相比，已选择了最后一个名表的记录（80K）。

我已经写了以下查询，但是现在它正常工作，因为它可以将每个姓氏带回相同的随机名称。

SELECT Lastname,
(select top 1 Firstname from [dbo].[Firstname_temp] order by newid()) as Firstname
FROM [TestDatabase].[dbo].[Lastname_temp]

我如何为每个姓氏行带回一个随机的名称，而不是出现对它们的所有一个名称？

事先感谢您的帮助。

原文

I am attempting to generate a random value from a table into a query being written against another table.
There are two tables in the database (database name "TestDatabase"):
a) [dbo].[Firstname_temp] - contains a list of firstnames (one column called "Firstname")
b) [dbo].[Lastname_temp] - contains a list of last names (one column called "Lastname")

I am using the Lastname_temp table as a basis to bring in a random first name for each surname within the query. The Lastname table has been chosen as that has a larger number of records (80K) compared to the Firstname table which has about 5k.

I have written the following query, however it is now working as expected as it is bringing back the same random firstname for every surname.

SELECT Lastname,
(select top 1 Firstname from [dbo].[Firstname_temp] order by newid()) as Firstname
FROM [TestDatabase].[dbo].[Lastname_temp]

How can I bring back a random firstname for every lastname row rather than the same firstname appearing against them all?

Thanks in advance for your help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

决绝 2025-02-10 06:41:37

问题是newId（）仅每个查询一次执行一次。没有什么可以停止SQL Server这样做的，因为子查询没有关联。要解决此问题，您可以将子查询关联。

解释：

newid（）是一个奇怪的功能。尽管它是一个副作用函数，但SQL Server不能保证它将每个输出行返回新值，除非newId（） call是 直接主选择，或以某种方式与查询的外部相关。

在您的查询中，内部子查询不相关，因此编译器知道它可以将其悬浮在查询的其余部分中。为了防止这种情况，您可以通过放置冗余相关性来迫使它在每个外排中计算它。例如：

SELECT
  Lastname,
  (
    select top (1)
      Firstname
    from [dbo].[Firstname_temp] f
    where l.Lastname = l.Lastname + ''
    order by newid()
  ) as Firstname
FROM [TestDatabase].[dbo].[Lastname_temp] l

The problem is that NEWID() is only being executed once per the whole query. There is nothing to stop SQL Server doing that, as the subquery is not correlated. To fix this, you can correlate the subquery.

To explain:

NEWID() is a weird function. Although it is a side-effecting function, SQL Server does not guarantee that it will return a new value per output row, unless either the NEWID() call is directly in the main SELECT, or it is somehow correlated to the outer part of the query.

In your query, the inner subquery is not correlated, so the compiler understands that it can hoist it out of the rest of the query. To prevent that, you can force it to calculate it on each outer row, by putting in a redundant correlation. For example:

SELECT
  Lastname,
  (
    select top (1)
      Firstname
    from [dbo].[Firstname_temp] f
    where l.Lastname = l.Lastname + ''
    order by newid()
  ) as Firstname
FROM [TestDatabase].[dbo].[Lastname_temp] l

回复收藏 0 原文

~没有更多了~