简单分组

这将为您提供将8837和8969作为备件的tabdata记录。您可以使用distinct,不需要group by。

SELECT  
 DISTINCT tabData.idData
 FROM    tabData 
 WHERE   EXISTS (SELECT *
                  FROM tabDataDetail
                 WHERE tabData.idData = tabDataDetail.fiData
                   AND tabDataDetail.fiSparePart = 8837)
   AND   EXISTS (SELECT *
                  FROM tabDataDetail
                 WHERE tabData.idData = tabDataDetail.fiData
                   AND tabDataDetail.fiSparePart = 8969)
 ORDER BY tabData.idData

试试这个

SELECT     tabData.idData
FROM         tabData INNER JOIN
                      tabDataDetail ON tabData.idData = tabDataDetail.fiData
WHERE tabDataDetail.fiSparePart IN (8837, 8969)
GROUP BY tabData.idData, tabDataDetail.fiSparePart
HAVING COUNT(distinct tabDataDetail.fiSparePart  ) > 1
ORDER BY tabData.idData

下面是一个例子

create table #bla (idData int, fiSparePart int)
insert #bla values(1,8837)
insert #bla values(1,8969)
insert #bla values(2,8837)
insert #bla values(2,8837)
insert #bla values(2,8837)



SELECT     idData
FROM        #bla
WHERE   fiSparePart IN (8837, 8969)
GROUP BY idData
HAVING COUNT(distinct fiSparePart) > 1

如果您从tabdata获得的只是主键(也作为外键存在于tabdatadetail中),则不需要两个表来完成此操作。假设tabdata是tabdatadetail的父表。

SELECT a.fiData
FROM (SELECT fiData FROM tabDataDetail WHERE fiSparePart = 8837) a
   INNER JOIN
     (SELECT fiData FROM tabDataDetail WHERE fiSparePart = 8969) b
   ON a.fiData = b.fiData

 SELECT     tabData.idData
 FROM         tabData INNER JOIN
                  tabDataDetail ON tabData.idData = tabDataDetail.fiData
 WHERE   tabDataDetail.fiSparePart IN (8837, 8969)
 GROUP BY tabData.idData, tabDataDetail.fiSparePart
 ORDER BY tabData.idData