时间戳

首页 > 解决方案 > JOIN 中的随机元素

mysql - JOIN 中的随机元素

问题描述

我这里有这段代码

INSERT INTO Directory.CatalogTaxonomy (`CatalogId`, `TaxonomyId`, `TaxonomyTypeId`, `IsApprovalRelevant`)
SELECT cat.CatalogId, dep.Id, @department_type, false
FROM Directory.Catalog cat
    JOIN (SELECT * FROM (
        SELECT * FROM Taxonomy.Department LIMIT 10
    ) as dep_tmp ORDER BY RAND() LIMIT 3) AS dep
WHERE cat.CatalogId NOT IN (SELECT CatalogId FROM Directory.CatalogTaxonomy WHERE TaxonomyTypeId = @department_type) 
    AND cat.UrlStatus = @url_status_green 
    AND (cat.StatusId = @status_published 
        OR cat.StatusId = @status_review_required);

问题是，它应该为每个目录从 Department 中获取前 10 个元素并随机选择其中的 3 个，然后添加到 CatalogDepartment 3 行，每行包含目录 ID 和分类 ID。但它会随机选择 3 个 Department 元素，然后将这 3 个元素添加到每个目录中。

当前结果如下所示：

1   000de9d7-af8b-4bac-bdbd-e6e361e5bc5e
1   001d4060-2924-4c75-b304-d780454f261b
1   001bc4b8-c1bc-498d-9aee-3825a40587d5
2   000de9d7-af8b-4bac-bdbd-e6e361e5bc5e
2   001d4060-2924-4c75-b304-d780454f261b
2   001bc4b8-c1bc-498d-9aee-3825a40587d5
3   000de9d7-af8b-4bac-bdbd-e6e361e5bc5e
3   001d4060-2924-4c75-b304-d780454f261b
3   001bc4b8-c1bc-498d-9aee-3825a40587d5

如您所见，每个目录只选择并重复了 3 个部门

标签： mysqlsqljoinrandomsql-order-by

解决方案

如果您认为查询：

SELECT * FROM (
  SELECT * FROM Taxonomy.Department LIMIT 10
) as dep_tmp 
ORDER BY RAND() LIMIT 3

您加入Directory.Catalog的每个目录返回 3 个不同的部门，那么您就错了。
此查询仅执行一次并返回 3 个随机部门，这些部门连接（总是相同的 3 个）到Directory.Catalog.
您可以做的是在您 CROSS JOIN 10 个部门后Directory.Catalog，为每个目录随机选择 3 个。
尝试这个：

INSERT INTO Directory.CatalogTaxonomy (`CatalogId`, `TaxonomyId`, `TaxonomyTypeId`, `IsApprovalRelevant`)
WITH cte AS (
  SELECT cat.CatalogId, dep.Id AS TaxonomyId, @department_type AS TaxonomyTypeId, false AS IsApprovalRelevant
  FROM Directory.Catalog AS cat
  CROSS JOIN (SELECT * FROM Taxonomy.Department LIMIT 10) AS dep
  WHERE cat.CatalogId NOT IN (SELECT CatalogId FROM Directory.CatalogTaxonomy WHERE TaxonomyTypeId = department_type) 
    AND cat.UrlStatus = @url_status_green 
    AND (cat.StatusId = @status_published OR cat.StatusId = @status_review_required);
)
SELECT t.CatalogId, t.TaxonomyId, t.TaxonomyTypeId, t.IsApprovalRelevant 
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY CatalogId ORDER BY RAND()) rn
  FROM cte
) t
WHERE t.rn <= 3

请注意：

SELECT * FROM Taxonomy.Department LIMIT 10

不保证您从 Department 获得前 10 个元素，因为表没有排序。

推荐阅读