数据库（六）——SQL语句 创建高级联结

1. 使用表别名

select RTRIM(vend_name) + '('+ RTRIM(vend_country)+')' as vend_title from Vendors order by vend_name;

2. SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。

select cust_name,cust_contact from Customers as C,Orders AS O,OrderItems AS OI where C.cust_id = O.cust_id and OI.order_num = o.order_num AND prod_id = 'rgan01';

需要注意，表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端

使用不同类型的联结。

1. 自联结

select c1.cust_id,c1.cust_name,c1.cust_contact from Customers as C1,Customers as C2 where c1.cust_name = c2.cust_name and C2.cust_contact = 'JIM JONES';

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。 2. 自然联结
自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（select*),而对其他列表使用明确的子集来完成。

select c.*,o.order_num,O.order_date,oi.prod_id,OI.quantity,OI.item_price from Customers as C,Orders as O ,OrderItems AS OI where 
C.cust_id = O.cust_id AND OI.order_num = O.order_num and prod_id = 'RGAN01';

3. 外联结

select Customers.cust_id,Orders.order_num from Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

4. 使用带聚集函数的联结

select Customers.cust_id,COUNT(Orders.order_num) as num_ord from Customers inner join Orders on Customers.cust_id = Orders.cust_id group by Customers.cust_id;

5. 使用联结和联结条件