SQL Server中的Over子句

Question

我有以下查询。

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子句的方法，但都没有成功。如何修改查询以便在指定时间范围内仅找到唯一的药品名称？

- wootscootinboogie

---

请解释一下你的第二个查询与第一个查询的关系。并且请详细说明你的问题“如何修改我的查询…”——除了drugName之外，你需要选择哪些其他列，并且如何确定应该显示哪个clmid（例如）与每个唯一的drugName一起？（我假设你不想要像“select distinct drugName from rx where patid=xxx ……”这样简单的东西！） - Lord Peter

第二个查询与第一个查询相关，因为它是表中的实际数据。您可以看到第二个查询中有五行数据。这五个行对应于第一张屏幕截图中的 distinctFamilies 列。但是，您可以看到，事实上只有三个独特的药品名称。 - wootscootinboogie

如果你在使用Oracle，count(distinct fieldname1) over(partition by fieldname2) 可以解决你的问题。然而，在SQL Server 8中，'count(distinct fieldname)'会报错，所以你需要尝试另一种方法。 - Saju

@Saju 我知道，这就是我在问的 :) - wootscootinboogie

Answer 1

尝试解决问题：

   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 - 由于您已经使用了它，因此将其保留。

- ic3b3rg