Sql server之常用三表联查举例

225025

　　1.创建表
　　
　　CREATE TABLE [dbo].[Customer](
　　[CustomerID] [int] NULL,
　　[CustomerName] [nvarchar](50) NULL
　　) ON [PRIMARY]
　　
　　CREATE TABLE [dbo].[Customer_Order](
　　[CustomerID] [int] NULL,
　　[OrderID] [int] NULL,
　　[Data] [datetime] NULL
　　) ON [PRIMARY]
　　
　　CREATE TABLE [dbo].[OrderID](
　　[OrderID] [int] NULL,
　　[Amount] [int] NULL
　　) ON [PRIMARY]
　　
　　
　　2.无订单的用户
　　select customerName
　　from customer C
　　left join Customer_Order CO
　　on c.customerid=co.customerid
　　where ISNULL(CO.customerid,0)!=0
　　group by c.customername
　　
　　3.每个人的订单成交量
　　select customername,isnull(SUM(Amount),0) as amounttotal from customer c
　　left join customer_order co on c.customerid=co.customerid
　　left join orderid o on co.orderid=o.orderid
　　group by customername
　　order by amounttotal
　　
　　
　　4.2009年谁成交的订单大于7000
　　select customername,isnull(SUM(Amount),0) as amounttotal,YEAR(co.data) as yearDate from customer c
　　left join customer_order co on c.customerid=co.customerid and YEAR(co.data)=2009
　　left join orderid o on co.orderid=o.orderid
　　group by customername,YEAR(co.data)
　　having isnull(SUM(Amount),0) >7000