Given the simple database structure:
鉴于简单的数据库结构：
Account
- Id (Primary Key)
Root
- Id (Primary Key)
- AccountId (FK to Account)
- Private (bit)
RootItem
- Id (Primary Key)
- AccountId (FK to Account)
- RootId (FK to Root)
Does anyone know how I can write a SQL statement to do the following?
有谁知道如何编写SQL语句来执行以下操作？
Given the fact I only know someone's Account.Id as int, lets say @AccountId = 1. Select a random Root record which has no attached RootItem records that have an AccountId that = @AccountID and where Root.Private = 0.
鉴于我只知道某人的Account.Id为int，让我们说@AccountId = 1.选择一个没有附加RootItem记录的随机Root记录，其中AccountId = @AccountID，其中Root.Private = 0。
This is for SQL Server, any help would be appreciated, I'm not all that good with SQL joins.
这是针对SQL Server的，任何帮助都会受到赞赏，我对SQL连接并不是那么好。
1 个解决方案
#1
2
select top 1 *
from
Root r
left join
RootItem ri
on
r.Id = ri.RootID and
ri.AccountId = @AccountID
where
r.Private = 0 and
ri.Id is null
order by
newid()
To eliminate Roots which have RootItems that match the account ID, we do the left join above, but then in the where clause, we ensure (ri.Id is null) that no match actually occurred. To select a row randomly, we're ordering by newid(), which will generate a new GUID for each row, and then just selecting the top 1.
要消除具有与帐户ID匹配的RootItem的Roots，我们在上面执行左连接，但是在where子句中，我们确保（ri.Id为null）实际上没有匹配。要随机选择一行，我们按newid（）排序，它将为每一行生成一个新的GUID，然后只选择前1。
If we need a stronger source of randomness, or some good statistical properties, we may have to look at working in CLR.
如果我们需要更强的随机性来源或一些好的统计属性，我们可能不得不考虑在CLR中工作。
#1
2
select top 1 *
from
Root r
left join
RootItem ri
on
r.Id = ri.RootID and
ri.AccountId = @AccountID
where
r.Private = 0 and
ri.Id is null
order by
newid()
To eliminate Roots which have RootItems that match the account ID, we do the left join above, but then in the where clause, we ensure (ri.Id is null) that no match actually occurred. To select a row randomly, we're ordering by newid(), which will generate a new GUID for each row, and then just selecting the top 1.
要消除具有与帐户ID匹配的RootItem的Roots，我们在上面执行左连接，但是在where子句中，我们确保（ri.Id为null）实际上没有匹配。要随机选择一行，我们按newid（）排序，它将为每一行生成一个新的GUID，然后只选择前1。
If we need a stronger source of randomness, or some good statistical properties, we may have to look at working in CLR.
如果我们需要更强的随机性来源或一些好的统计属性，我们可能不得不考虑在CLR中工作。