1.A 和B 两张表,A中有B表的外键,B表中有A表中的外键,求A表中,B表中没有的数据行,B表中,A表没有的数据行。
select * from a where not exists (select 1 from b where a.主键=b.外键)
select * from b where not exists (select 1 from a where b.主键=a.外键)
2.员工表Employees,里面有字段 EmployeeID,EmployeeName,
销售表Sales 里面有字段EmployeeID,productID,salesamount。
例如:EmployeeID,productID,salesamount
1 1 100
1 2 80
2 1 39
2 2 50
EmployeeID,EmployeeName
1 Jack
2 Mary
问:查出把所有负责的产品销售50以上的销售员工姓名。
select a.EmployeeName from Employees as a, Sales as b
where a.EmployeeID=b.EmployeeID and not exixts
(select 1 from b where EmployeeID=b.EmployeeID and salesmount<50