SQL如何选择每个数据持续时间小于300000 ms的产品

Question

在以下查询中，我试图抓取upcs，其中所有isrcs的持续时间小于300000 ms，其中曲目数量为11，并且拥有的领土不在31,201,41,125)与此查询

select  r.UPC ,r.Id, res.ISRC , res.Duration ,COUNT( res.ISRC) from Release r 
inner join ReleaseResource rr on rr.ReleaseId=r.Id
inner join Resource res on res.Id=rr.ResourceId
inner join ReleaseTerritory rt on rt.ReleaseId=r.id
where   not r.OwningTerritoryId in (31,201,41,125) and res.Duration<5*60000 and  r.TrackCount=11 and rt.IsDeleted=0
group by r.UPC ,r.Id, res.ISRC , res.Duration
having COUNT( distinct rt.TerritoryId)=10  
order by r.upc

我得到的结果是好的，除了isrc，其中查询只显示少于300000 ms的isrc，但是当我查看upc内部时，我发现还有其他isrc的持续时间超过300000 ms。你知道我应该修改什么，以便只有所有isrcs小于300000 ms的upcs吗?谢谢

Answer 1

这是因为您在分组之前过滤掉了'持续时间小于300000 ms' ..从WHERE中删除过滤器并在HAVING

中尝试此操作

select  r.UPC ,r.Id, res.ISRC , res.Duration ,COUNT( res.ISRC) from Release r 
inner join ReleaseResource rr on rr.ReleaseId=r.Id
inner join Resource res on res.Id=rr.ResourceId
inner join ReleaseTerritory rt on rt.ReleaseId=r.id
where   not r.OwningTerritoryId in (31,201,41,125) and  r.TrackCount=11 and rt.IsDeleted=0
group by r.UPC ,r.Id, res.ISRC , res.Duration
having COUNT( distinct rt.TerritoryId)=10  AND MAX(res.Duration)<5*60000
order by r.upc