php - 选择字段值至少出现在其他4行中的行

我有一个SQL语句，它可以将我带到如下结果表。。。

categoryID | subCategoryID | categoryName
-------------------------------------------
1          | 2             |  Animals & Pets
1          | 7             |  Animals & Pets
1          | 10            |  Animals & Pets
1          | 11            |  Animals & Pets
4          | 0             |  Books & Magazines
4          | 0             |  Books & Magazines
4          | 0             |  Books & Magazines
4          | 0             |  Books & Magazines
4          | 31            |  Books & Magazines
4          | 32            |  Books & Magazines
4          | 33            |  Books & Magazines
5          | 0             |  Chemist
6          | 0             |  Cloths & Accessories
6          | 0             |  Cloths & Accessories
6          | 656           |  Cloths & Accessories
7          | 0             |  Collectables
7          | 0             |  Collectables
7          | 0             |  Collectables
8          | 0             |  Computer
8          | 0             |  Computer
8          | 0             |  Computer
8          | 0             |  Computer
8          | 0             |  Computer
8          | 0             |  Computer
8          | 56            |  Computer
8          | 60            |  Computer
8          | 61            |  Computer

我现在只想得到subCategoryID列中至少有4个不同ID共享同一categoryID的行，然后按categoryID对它们进行分组。例如把上表变成。。。

categoryID | subCategoryID | categoryName
-------------------------------------------
1          | 2             |  Animals & Pets
4          | 0             |  Books & Magazines
8          | 0             |  Computer

到目前为止，我的SQL是。。

SELECT
    listing.categoryID,
    listing.subCategoryID,
    categoryName
FROM listing
LEFT JOIN productInfo USING (listingID)
LEFT JOIN sectionCategory USING (categoryID)
WHERE listing.categoryID > 0
AND listing.listingStatus = 'A'
AND listing.pauseReason = 'A'
AND productInfo.quantity > 0
ORDER BY categoryID, subCategoryID

我试过用。。。

SELECT
    listing.categoryID,
    categoryName
FROM listing
LEFT JOIN productInfo USING (listingID)
LEFT JOIN sectionCategory USING (categoryID)
WHERE listing.categoryID > 0
AND listing.listingStatus = 'A'
AND listing.pauseReason = 'A'
AND productInfo.quantity > 0
GROUP BY listing.categoryID
HAVING count(*) >= 4
ORDER BY RAND()
LIMIT 6

但它似乎只删除了列表计数小于4的类别。有什么想法吗？

最佳答案

使用Having子句筛选至少有4个不同categoryID的subCategoryID。试试这个。

select categoryID , categoryName
from yourtable
group by categoryID , categoryName
having count(distinct subCategoryID)>= 4

但我不确定你如何在预期的输出中categoryID=4。
如果您希望每个subCategoryID至少有4个category不能distinct，请使用此选项。

select categoryID , categoryName
from yourtable
group by categoryID , categoryName
having count(subCategoryID)>= 4

关于php - 选择字段值至少出现在其他4行中的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27976360/