以sqlserver2000数据库NorthWind中的Orders表和Customers表为例,Orders是主表,以字段CustomerID建立依赖关系,按下面要求写出一条sql语句:1、从Orders表中选出Freight、ShipName、ShipAddress、ShipPostalCode这几个字段,
2、从Customers表中选出CompanyName、ContactName、Phone、Fax这几个字段,
3、要求选出的记录集中CustomerID不重复
4、从Orders表中选出ShippedDate最大的那条记录,如果一个CustomerID对应一条以上ShippedDate相同的记录,则从选出的记录集中再选出OrderID最大的那一条记录。即一个CustomerID只能对应Orders表中的一条记录。要把以上要求整合成一条sql语句,应该怎么写?
2、从Customers表中选出CompanyName、ContactName、Phone、Fax这几个字段,
3、要求选出的记录集中CustomerID不重复
4、从Orders表中选出ShippedDate最大的那条记录,如果一个CustomerID对应一条以上ShippedDate相同的记录,则从选出的记录集中再选出OrderID最大的那一条记录。即一个CustomerID只能对应Orders表中的一条记录。要把以上要求整合成一条sql语句,应该怎么写?
select a.Freight,a.ShipName,a.ShipAddress,a.ShipPostalCode,
b.CompanyName,b.ContactName,b.Phone,b.Fax from Orders a,Customers b
where a.CustomerID=b.CustomerID)
aa
可以在sqlserver2000数据库中的NorthWind中试一下,最后的记录数应该等于在Orders表中distinct( CustomerID)后所得的记录数,好像是89条。
select a.Freight, a.ShipName, a.ShipAddress, a.ShipPostalCode,
(select CompanyName from Customers where Customers.CustomerID=a.CustomerID) as CompanyName,
(select ContactName from Customers where Customers.CustomerID=a.CustomerID) as ContactName,
(select Phone from Customers where Customers.CustomerID=a.CustomerID) as Phone,
(select Fax from Customers where Customers.CustomerID=a.CustomerID) as Fax
from Orders a
where not exists(select 1 from Orders where CustomerID=a.CustomerID and a.ShippedDate<ShippedDate)
要是Orders表里面的CustomerID是 Customers表的子集 那么Customers是主表,Orders是子表
反过来 Customers表的CustomerID 是Orders表CustomerID的子集 那么 Orders是主表,Customers是子表
Customers(客户信息表)
Orders(客户订单表)
从这两个表的字面意思来看,我主要想查询客户的订单,客户订单表应该是主表。
(select CompanyName from Customers where Customers.CustomerID=a.CustomerID) as CompanyName,
(select ContactName from Customers where Customers.CustomerID=a.CustomerID) as ContactName,
(select Phone from Customers where Customers.CustomerID=a.CustomerID) as Phone,
(select Fax from Customers where Customers.CustomerID=a.CustomerID) as Fax
from Orders a
where not exists(select 1 from Orders b where b.CustomerID=a.CustomerID and a.ShippedDate<b.ShippedDate
and not exists(select 1 from Orders where ShippedDate=b.ShippedDate and b.OrderID<OrderID)
)