选择字段值至少出现在其他4行中的行
选择字段值至少出现在其他4行中的行
提问于 2015-01-16 02:22:28
我有一条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的subCategoryID列中至少有4个不同ID的行，然后按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的类别。有什么想法吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-01-16 02:26:27
使用Having子句筛选至少有4个不同subCategoryID的categoryID。尝尝这个。
select categoryID , categoryName
from yourtable
group by categoryID , categoryName
having count(distinct subCategoryID)>= 4但不确定您如何在预期的输出中categoryID=4。
如果您希望每个category至少有4个category可能不是distinct，那么请使用此方法。
select categoryID , categoryName
from yourtable
group by categoryID , categoryName
having count(subCategoryID)>= 4Stack Overflow用户
发布于 2015-01-16 02:37:13
加入临时桌怎么样？
DROP TEMPORARY TABLE IF EXISTS categoryIDs; CREATE TEMPORARY TABLE IF NOT EXISTS categoryIDs as( select t.categoryID from (select categoryID from table where distinct(categoryID) >=4 group by categoryID)t );
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27976360
复制相关文章
相似问题
腾讯云开发者
