当 COUNT 另一个表时，从表中选择 2 行

发布于 2024-09-06 00:03:43 字数 573 浏览 5 评论 0原文

这是我当前拥有的代码：

   SELECT `A`.* 
     FROM `A`
LEFT JOIN `B` ON `A`.`A_id` = `B`.`value_1` 
    WHERE `B`.`value_2` IS NULL 
      AND `B`.`userid` IS NULL
 ORDER BY RAND() LIMIT 2

当前应该做的是，当选择的 2 行 A_id 不在 value_1 中时，从 A 中选择 2 行 或 B 中的 value_2。 B 中的行特定于具有 userid 的各个用户。

我需要做的是使它也如此，以便还检查 B 中是否已有 N 行与 A_id 匹配（在 value_1 中，或 value_2）和 userid，如果超过 N 行，则不会选择 A 行。

原文

Here is the code that I currently have:

   SELECT `A`.* 
     FROM `A`
LEFT JOIN `B` ON `A`.`A_id` = `B`.`value_1` 
    WHERE `B`.`value_2` IS NULL 
      AND `B`.`userid` IS NULL
 ORDER BY RAND() LIMIT 2

What it currently is supposed to do is select 2 rows from A when the 2 rows A_id being selected are not in value_1 or value_2 in B. And the rows in B are specific to individual users with userid.

What I need to do is make it also so that also checks if there are already N rows in B matching a A_id (either in value_1, or value_2) and userid, and if there are more than N rows, it doesn't select the A row.

分享到QQ

分享到微博

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

发布评论

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

梦明 2024-09-13 00:03:43

以下将处理您的第一个请求：

Select ...
From A
    Left Join B
        On ( B.value_1 = A.A_id Or B.value_2 = A.A_id )
            And B.userid = @userid
Where B.<non-nullable column> Is Null

部分技巧是将您的条件移至 Left Join 的 ON 子句中。我不确定您请求的第二部分与第一部分是否相符。如果 B 中没有与给定用户的 value_1 或 value_2 匹配的行，则根据定义，行计数将为零。您是否希望 B 中只能有最大行数匹配给定条件？如果是这样，那么我会像这样编写查询：

Select ...
From A
Where   (
        Select Count(*)
        From B B2
        Where ( B2.value_1 = A.A_id Or B2.value_2 = A.A_id )
            And B2.userid = @userid
        ) <= @MaxItems

The following would handle your first request:

Select ...
From A
    Left Join B
        On ( B.value_1 = A.A_id Or B.value_2 = A.A_id )
            And B.userid = @userid
Where B.<non-nullable column> Is Null

Part of the trick is moving your criteria into the ON clause of the Left Join. I'm not sure how the second part of your request fits with the first part. If there are no rows in B that match on value_1 or value_2 for the given user, then by definition that row count will be zero. Is it that you want it be the situation where there can only be a maximum number of rows in B matching on the given criteria? If so, then I'd write my query like so:

Select ...
From A
Where   (
        Select Count(*)
        From B B2
        Where ( B2.value_1 = A.A_id Or B2.value_2 = A.A_id )
            And B2.userid = @userid
        ) <= @MaxItems

回复收藏 0 原文

~没有更多了~