这似乎很简单,但我不知道如何做到这一点或正确的描述,以正确地谷歌它:(
简单地说,有一个表:
PatientID | Date | Feature_of_Interest...
我想为多次就诊的患者绘制一些结果,当他们有兴趣的特征时。按感兴趣的特征进行过滤没有问题,但是我只希望结果查询包含有多个条目的患者。
SELECT PatientID,Date,...
FROM myTable
WHERE Feature_Of_Interest is present
AND (Filter out PatientID's that only appear once)
所以-只是不确定如何处理这个。我试着做:
WITH X AS (Above SELECT, Count(*),...,Group by PatientID)
然后重新运行查询,但它没有工作。如果需要的话,我可以把这些都贴出来,但我给人的印象是,我正在接近这个完全落后,所以现在将推迟。
使用SQL Server 2008
试试这个:
WITH qry AS
(
SELECT a.*,
COUNT(1) OVER(PARTITION BY PatientID) cnt
FROM myTable a
WHERE Feature_Of_Interest = 'present '
)
SELECT *
FROM qry
WHERE cnt >1
您需要加入子查询
JOIN (
SELECT
PatientID
FROM myTable
WHERE Feature_Of_Interest is present
GROUP BY PatientID
HAVING COUNT(*) > 1
) s ON myTable.PatientID = s.PatientID
您可以从访问计数查询开始:
SELECT PatientID, COUNT(*) as numvisits FROM myTable
GROUP BY PatientID HAVING(numvisits > 1);
然后您可以通过连接。
快速回答,因为我要上床睡觉了,所以这是未经测试的代码,但简而言之,您可以使用子查询…
SELECT PatientID,Date,...
FROM myTable
WHERE Feature_Of_Interest is present
AND patientid in (select PatientID, count(patientid) as counter
FROM myTable
WHERE Feature_Of_Interest is present group by patientid having counter>1)
我很惊讶你的尝试没有成功,它听起来有点像它应该有,除了你没有说计数> 1,所以它可能只是返回所有的
您应该能够通过使用类似于以下的窗口函数获得所需的内容:
WITH ctePatient AS (
SELECT PatientID, Date, SUM(1) OVER (PARTITION BY PatientID) Cnt
FROM tblPatient
WHERE Feature_Of_Interest = 1
)
SELECT *
FROM ctePatient
WHERE Cnt > 1