为什么我不能对子查询应用条件? [英] Why can't I apply a criteria on a sub-query?

问题描述

我想要做的是:

select Store.Id, (select COUNT(*) from StoreProduct
where StoreProduct.Store_id = Store.Id) as _count from Store where _count > 3

SQL Server 说它无效，因为列名_count"无效.为什么我声明它是无效的?

SQL Server says it's invalid because the column name '_count' is invalid. Why it's invalid if I'm declaring it?

推荐答案

select Id as StoreId, count(*) as ProductCount
from StoreProduct
group by Id
having count(*) > 3

<小时>

另一种方式

---

Another way

select S.Id, count(SP.ProductId) as ProductCount
from Store S
left join StoreProduct SP on (SP.Store_id = S.Id)
group by S.Id
having COUNT(SP.ProductId) > 3

假设，StoreProduct 表中的唯一列名为 ProductId

Which, assumes, the unique column in your StoreProduct table is named ProductId

这篇关于为什么我不能对子查询应用条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！