1.select ClientId,clientname from Client a inner join Opportunity b on a.ClientId=b.ClientId where b.OppDate='2004-8-1' and b.OppDate=2004-9-30' and b.OppName<>'发现机会'2.select disinct ClientId,clientname from Client a inner join Opportunity b on a.ClientId=b.ClientId where b.OppDate='2004-8-1' and b.OppDate=2004-9-30' and b.OppName='发现机会'
select * from client where cilentid in
(select a.ClientId from client a,opportunity b where a.clientid=b.clientid and b.oppdate>'2004-08-01' and b.oppdate<'2004-09-30' group by having count(*)>=1)
select * from client where cilentid in
(select a.ClientId from client a,opportunity b where a.clientid=b.clientid and b.oppdate>'2004-08-01' and b.oppdate<'2004-09-30' and b.OppName='发现机会' group by having count(*)>=1)
----------- Question 1-----------
SELECT *
FROM Client
WHERE (ClientId NOT IN
(SELECT ClientId
FROM Opportunity
WHERE OppDate > '2004-08-01' AND OppDate < '2004-09-30'))
----------- Question 2 -----------
SELECT *
FROM Client
WHERE (ClientId IN
(SELECT a.ClientId
FROM client a, Opportunity b
WHERE a.ClientId = b.ClientId AND b.OppDate > '2004-08-01' AND
b.OppDate < '2004-09-30'
GROUP BY a.ClientId
HAVING COUNT(b.OppId) >= 1))