Sql Server 创建高级联结

--自联结 ：自己和自己进行连接 如一个表进行自己对自己联结。
select * from Products

select prod_id+vend_id from Products;

select RTRIM(Prod_id) + RTRIM(vend_id) from Products;

select RTRIM(Prod_id) + '('+ ltrim(vend_id)+')' from Products; --RTRIM 去除右侧多余空白 LTRIM 去除左侧多余空白

select RTRIM(Prod_id) + '('+ rtrim(vend_id)+')' from Products;

select cust_id,cust_name,cust_contact from Customers where cust_contact='Jim Jones'

select cust_id,cust_name,cust_contact from Customers where CUST_NAME=(select cust_name from customers where cust_contact='Jim Jones')
select * from Customers;

--自联结 ：自己和自己进行连接 如一个表进行自己对自己联结。

select c1.cust_id,c1.cust_name,c1.cust_contact from Customers c1,Customers c2 where c1.cust_name=c2.cust_name and c2.cust_contact='Jim Jones'

select * from Customers c1,Customers c2 where c1.cust_name=c2.cust_name and c2.cust_contact='Jim Jones'

select * from Customers c1,Customers c2 where c1.cust_id=c2.cust_id and c2.cust_contact='Jim Jones'

--自然联结：自然的检索出数据，没有重复的列。 一般用于 对第一个表使用 (SELECT *) 其他表明确列出子集。
select C.*,O.order_num,O.order_date,OI.item_price,OI.quantity,OI.prod_id from Customers AS C,OrderItems AS OI,Orders AS O WHERE C.cust_id=O.cust_id AND O.order_num=OI.order_num AND OI.prod_id='RGAN01'

select cust_name ,(select COUNT(*) from Orders where Orders.cust_id=Customers.cust_id) from Customers
select cust_name ,(select COUNT(*) from Orders where Orders.cust_id=Customers.cust_id) from Customers group by cust_name ,cust_id;

select cust_name from Customers where cust_id in(select Orders.cust_id from Orders)

select Orders.order_num ,(select COUNT(*) from Customers where Customers.cust_id=Orders.cust_id) from Orders

select * from Products;

select * from OrderItems;

--select P.*,(SELECT quantity FROM OrderItems WHERE OrderItems.prod_id = P.prod_id) from Products P

SELECT O.quantity ,(SELECT COUNT(*) FROM Products WHERE Products.prod_id=O.prod_id) FROM OrderItems O

SELECT O.prod_id, O.quantity , P.prod_name,P.prod_price FROM OrderItems O ,Products P WHERE P.prod_id=O.prod_id
SELECT O.prod_id, O.quantity , P.prod_name,P.prod_price FROM OrderItems O ,Products P

select Customers.cust_name,orders.order_num from Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_id;

select cust_name ,(select COUNT(*) from Orders where Orders.cust_id=Customers.cust_id) from Customers

--外联结 和内联结 外联结： 检索出的数据还包括没有关联行的行。 内联结：只检索关联行的数据。
--可以理解为 联结时 联结的行不成立也可以检索出来。 比如NULL。
--如果是用的左外联结 以左表为主， 右外联结 以右表为主。

--全外联结： FULL OUTER JOIN ON full outer join on

--左联结和右联结可以互换使用。 可以调整表名的顺序来变化使用。 A表和B表， 左联结 A,B 右联结 B,A
-- 语法 左外联结： LEFT OUTER JOIN ON left outer join on 右外联结： RIGHT OUTER JOIN ON right outer on
--外联结
select cust_name,order_num from Customers LEFT OUTER JOIN Orders ON Customers.cust_id=Orders.cust_id; --左外联结

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

select cust_name ,order_num from Customers RIGHT OUTER JOIN Orders ON Customers.cust_id=Orders.cust_id; --右外联结

select cust_name ,order_num from Customers FULL OUTER JOIN Orders ON Customers.cust_id=Orders.cust_id; --全外联结

select cust_name ,order_num from Orders FULL OUTER JOIN Customers ON Orders.cust_id=Customers.cust_id; --全外联结
select * from Orders;

--使用带聚集函数的联结

select Customers.cust_id,COUNT(Orders.order_num) from Customers inner join orders on customers.cust_id=Orders.cust_id group by customers.cust_id

select * from Customers;