请教sql 开窗函数如何处理条件呢？
比如我想查找商品表中，每种类型各有多少种商品，条件是每类商品数量不能小于10.如何写？
select COUNT(goodstype) as goodscount,goodstype from tbGoods group by goodstype having COUNT(goodstype)>10
用partition by如何处理
select distinct COUNT(goodstype) over(partition by goodstype) as goodscount,goodstype from tbGoods？？？
------解决方案--------------------
- SQL code
declare @tbGoods table (goodstype varchar(1))insert into @tbGoodsselect 'a' union allselect 'a' union allselect 'a' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b' union allselect 'b'select COUNT(1) as goodscount,goodstype from @tbGoods group by goodstype having COUNT(goodstype)>10/*goodscount goodstype----------- ---------12 b*/SELECT ROW_NUMBER() OVER (PARTITION BY goodstype ORDER BY (SELECT 1)) AS rid,* FROM @tbGoods/*rid goodstype-------------------- ---------1 a2 a3 a1 b2 b3 b4 b5 b6 b7 b8 b9 b10 b11 b12 b*/