sql - SQL Server中的Over子句

我有以下查询

select * from
(
        SELECT distinct
        rx.patid
       ,rx.fillDate
       ,rx.scriptEndDate
       ,MAX(datediff(day, rx.filldate, rx.scriptenddate)) AS longestScript
       ,rx.drugClass
       ,COUNT(rx.drugName) over(partition by rx.patid,rx.fillDate,rx.drugclass) as distinctFamilies
       FROM [I 3 SCI control].dbo.rx
       where rx.drugClass in ('h3a','h6h','h4b','h2f','h2s','j7c','h2e')
       GROUP BY rx.patid, rx.fillDate, rx.scriptEndDate,rx.drugName,rx.drugClass

) r
order by distinctFamilies desc

产生看起来像

这意味着在表格的两个日期之间，patID应该有5个唯一的药物名称。但是，当我运行以下查询时：

select distinct *
    from rx
    where patid = 1358801781 and fillDate between '2008-10-17' and '2008-11-16' and drugClass='H4B'

我有一个返回的结果集看起来像

您可以看到，尽管实际上在2008-10-17和2009-01-15之间的第二次查询返回了五行，但只有三个唯一名称。我尝试了各种修改over子句的方法，所有方法都具有不同程度的不成功。如何更改查询，以便仅在为每一行指定的时间范围内找到唯一的drugName？

最佳答案

拍摄一下：

   SELECT DISTINCT
  patid,
  fillDate,
  scriptEndDate,
  MAX(DATEDIFF(day, fillDate, scriptEndDate)) AS longestScript,
  drugClass,
  MAX(rn) OVER(PARTITION BY patid, fillDate, drugClass) as distinctFamilies
FROM (
  SELECT patid, fillDate, scriptEndDate, drugClass,rx.drugName,
  DENSE_RANK() OVER(PARTITION BY patid, fillDate, drugClass ORDER BY drugName) as rn
  FROM [I 3 SCI control].dbo.rx
  WHERE drugClass IN ('h3a','h6h','h4b','h2f','h2s','j7c','h2e')
)x
GROUP BY x.patid, x.fillDate, x.scriptEndDate,x.drugName,x.drugClass,x.rn
ORDER BY distinctFamilies DESC

不知道DISTINCT是否真的必要-因为您已经使用过，所以将其保留。