SQL菜鸟请教

[cppfaq]

Table Evidence
ID EvidenceClass Property Value
1 class_a prop_a 1
2 class_a prop_b 2
3 class_a prop_c 3
4 class_a prop_a 4
5 class_a prop_b 5
6 class_a prop_c 6
7 class_b prop_a 111
8 class_c prop_e 20
9 class_c prop_f 20
10 class_c prop_g 20

Table EventEvidenceRequirement
EventID EvidenceID
1 1
1 2
1 3
1 4
2 1
2 2
2 5

Table CollectedEvidence
ComputerID EvidenceID InstanceName
1 1 aaa
1 2 aaa
1 3 aaa
1 4
1 1 bbb
2 2 aaa
2 3 aaa
2 4
2 1 bbb

需求是要找出所有computer上的event.
需要注意的是同一个computer上可能存在多个指定evidenceclass的instance.
比如：
event1,要求computer上要有evidence 1,2,3,4。computer 2上有evidence 1,2,3,4。但是由于
1) 1,2,3都属于class_a
2) computer 2上的evidence 1来自class_a的instance bbb, 2和3来自instance aaa.
所以，computer 2不满足event 1的要求。
因此，输出为：
Event Computer
1 1

[还在加载中灬]

SELECT T1.ComputerID,T2.EventID
FROM CollectedEvidence T1
	JOIN EventEvidenceRequirement T2 ON T1.EvidenceID=T2.EvidenceID
WHERE
	NOT EXISTS(SELECT 1 FROM EventEvidenceRequirement T3 WHERE T2.EventID=T3.EventID AND
	NOT EXISTS(SELECT 1 FROM CollectedEvidence T4 WHERE T1.ComputerID=T4.ComputerID
		AND(ISNULL(T4.InstanceName,'')=''OR T1.InstanceName=T4.InstanceName)AND T4.EvidenceID=T3.EvidenceID))
GROUP BY T1.ComputerID,T2.EventID

不知道 你 CollectedEvidence 表中放空的是 '' 还是 NULL，不过以上语句都兼容